Mysql – Which isolation level do I need

database-designisolation-levelMySQL

I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database.

This is my situation:

Let's say I have two group of users:

  1. Group A (executes selects)
  2. Group B (executes inserts and updates)

Every individual in group A is executing huge select query that takes 5 minutes and in the meanwhile a couple members from group B are inserting and updating records.

Which database isolation do I need to achieve:

  1. The select queries that are executed by people from Group A should
    NOT cause a lock to eachother. So simultaneously reads should be
    allowed, they must be able to access the same data at the same time.
  2. The select queries that are executed by people from Group A should
    NOT cause a lock to people from Group B who are inserting and
    updating. So simultaneously writes and reads should be allowed and
    people from Group B shouldn't be waiting 5 minutes until the individual select
    queries from Group A are finished.
  3. The inserted and updated records should be included in the select
    query results from Group A.

Best Answer

A slow SELECT that can be sped up by adding a composite index or reformulating the query will mostly avoid getting blocked.

Ditto for inefficient INSERTs and other queries.

What is your setting for tx_isolation? Changing that to READ UNCOMMITTED for the long SELECT may help.

Is this a Data Warehouse application and the SELECT is a 'report'? If so, consider building and maintaining a Summary table. This makes reports much faster.

Consider Master-Slave setup, with the long SELECTs going to the Slave.

You are using InnoDB, not MyISAM, correct?

If you want more specific advice, please provide (at least) SHOW CREATE TABLE and the SELECT.

Related Question