MySQL 5.6 Transaction Isolation Levels

In my previous post on MySQL 5.6 Transactions, I demonstrated how transactions can be started, committed, rolled back (either partially or fully) and how to change the autocommit functionality of MySQL. In the post I’ll describe how MySQL handles multiple transactions running at the same time, through the use of Transaction Isolation Levels.

Remember that the topics of transactions and transaction isolation levels were not on the 5.0 Developer exams, and are new topics for the 5.6 Developer exam. There is however information on this topic in Chapter 29 of the MySQL 5.0 Certification Study Guide (in the DBA part of the book) and also in Chapter 2 of the MySQL Developer’s Library.

Transaction Isolation

Transaction isolation levels control how multiple transactions can run at the same time, and whether or not they can see the changes being made by the other transactions. When transactions run simultaneously, there are three potential problems that might arise:

  1. Dirty reads
    When a change made by one transaction can be seen by another transaction before the transaction has been committed.
  2. Nonrepeatable reads
    When a transaction runs an identical SELECT statement twice and gets different results.
  3. Phantom rows
    When one transaction runs a SELECT, and then another transaction performs an INSERT, and then the first transaction runs the same SELECT again and sees the new row.

MySQL InnoDB tables handle these three problems by implementing four transaction isolation levels:

  1. READ UNCOMMITTED
    A transaction can see changes to rows made by other transactions even before the transactions have been committed.
  2. READ COMMITTED
    A transaction can only see changes to rows made by other transactions when they have been committed.
  3. REPEATABLE READ
    This is the default setting. If a transaction performs a given SELECT statement twice, the result will always be the same, even if another transaction has changed rows in the meantime.
  4. SERIALIZABLE
    This has the same functionality as REPEATABLE READ but with an added twist. Rows being examined by one transaction can not be modified by another transaction until the first transaction completes.

There’s a great table in the MySQL Developer’s Library book which summarizes each isolation levels and the problems they allow:

Isolation LevelDirty ReadsNonrepeatable ReadsPhantom Rows
READ UNCOMMITTEDYesYesYes
READ COMMITTEDNoYesYes
REPEATABLE READNoNoNo
SERIALIZABLENoNoNo

You can check the current transaction isolation level by examining the system variable:

SELECT @@tx_isolation;

And you can set is as follows:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

This sets the isolation level to READ COMMITTED on the server. Any new clients connecting to the server will use this isolation level. Only users with the SUPER privilege can set the global isolation level.

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

This sets the isolation level to SERIALIZABLE for the current client’s session. The isolation level will revert back to the server’s default if the client disconnects and reconnects.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This sets the isolation level to READ UNCOMMITTED for the next transaction issued by the current client.

Again, the best way to see this working is to use two different MySQL prompts, start transactions in one prompt, and view the results in the other. The attached script file has some demo queries you can use to play with isolation levels.