0

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.
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.