Understanding Innodb Transaction Isolation

The MySQL Innodb storage engine provides ACID compliance, the ‘I’ being isolation. There are four states defined in MySQL with the tx_isolation system variable, READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ and SERIALIZABLE.

Generally MySQL installations do not modify the default value of tx_isolation = REPEATABLE-READ, however I have seen with a number of clients the default value has been changed to READ-COMMITTED. When I attempt to identify the reason why, I have always received the same reason. Oracle uses a default transaction isolation of READ-COMMITTED. See how Oracle Manages Data Concurrency and Consistency for more information.

However, while the literal string is the same, the actual implementation of READ-COMMITTED in Oracle more closely represents the REPEATABLE-READ in MySQL.

The following demonstrates what you can expect to see between the operation of these two modes.

I am still unclear of the specific benefits in general terms for all environments. Review of the The InnoDB Transaction Model and Locking and specifically Consistent Nonlocking Reads provides “With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot” which indicates that for certain workloads the reduced locking is a benefit.

Every environment is different and ultimately the actual transaction statements will determine what options and benefits work best.

Comments

Read-commited can really help with some locking issues. However, I have decided not to change the isolation level, because any other levels are far less tested than repeatable-read.

For example, this serious bug: http://bugs.mysql.com/bug.php?id=45357
Both our masters crashed in cycle until I realized that the new transaction isolation set for a session in our application is the problem.

What’s more, you cannot use read-commited in case you need mixed/statement replication. If you use mk-table-sync, statement replication is a must.

I’m bit disappointed with this – why there are these transaction levels, if I cannot use them safely in real applications?

Thanks for this analysis and demonstration. Without diving into the code, it makes sense to me that READ COMMITTED would place less burden on an MVCC database system, because the engine now only has to consider and manage one “version of the truth” at any point in time, whereas the default mode has to consider the point in time for all open transactions.

The strongest benefit for READ COMMITTED is that you can get consistent backups in a fast moving system, which may be why it’s the default. I can definitely see why you’d want to make READ COMMITTED your active setting, but like the previous commenter I prefer to say with the well-tested settings unless there is a remarkable performance difference. In the case of transaction isolation, I have yet to see that on my workloads.