13.3.6 SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
  {
       REPEATABLE READ
     | READ COMMITTED
     | READ UNCOMMITTED
     | SERIALIZABLE
   }

This statement sets the transaction isolation level, used for operations on InnoDB tables.

Scope of the Isolation Level

You can set the isolation level globally, for the current session, or for the next transaction:

A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction.

SET TRANSACTION ISOLATION LEVEL without GLOBAL or SESSION is not permitted while there is an active transaction:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction isolation level can't be changed
while a transaction is in progress

To set the global default isolation level at server startup, use the --transaction-isolation=level option to mysqld on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mysqld] section of an option file:

[mysqld]
transaction-isolation = REPEATABLE-READ

It is possible to check or set the global and session transaction isolation levels at runtime by using the tx_isolation system variable:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';

Details and Usage of Isolation Levels

InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.

For full information about how these isolation levels work with InnoDB transactions, see Section 14.5.2, “The InnoDB Transaction Model and Locking”. In particular, for additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see Section 14.5.6, “InnoDB Record, Gap, and Next-Key Locks” and Section 14.5.8, “Locks Set by Different SQL Statements in InnoDB.

The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.