Skip navigation links

Additional languages
12.4.7 XA Transactions »
Section Navigation      [Toggle]

12.4.6. SET TRANSACTION Syntax

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

This statement sets the transaction isolation level for the next transaction, globally, or for the current session:

  • The default behavior (without any SESSION or GLOBAL keyword) is to set the isolation level for the next (not started) transaction.

  • With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions created from that point on (but not for existing connections). You need the SUPER privilege to do this.

  • With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.

To set the initial default global isolation level for mysqld, use the --transaction-isolation option. See Section 5.1.2, “Command Options”.

For information about InnoDB and transaction isolation level, see Section 13.6.9.3, “InnoDB and TRANSACTION ISOLATION LEVEL”. InnoDB supports each of the levels described here. The default level is REPEATABLE READ. See also Section 13.6.9.8, “Locks Set by Different SQL Statements in InnoDB”, for additional information about how InnoDB uses locks to execute various types of statements.

In MySQL 6.0, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for non-matching rows are released after MySQL has evaluated the WHERE condition.

A detailed list of the transaction levels supported by MySQL and the various storage engines follows:

  • READ UNCOMMITTED

    SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

  • READ COMMITTED

    A somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements lock only the index records, not the gaps before them, and thus allow the free insertion of new records next to locked records. UPDATE and DELETE statements using a unique index with a unique search condition lock only the index record found, not the gap before it. In range-type UPDATE and DELETE statements, InnoDB must set gap locks or next-key (index record plus gap) locks and block insertions by other users to the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.

    Consistent reads behave as in Oracle: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 13.6.9.4, “Consistent Non-Locking Read”.

    Note

    If you use READ COMMITTED (which is equivalent to enabling innodb_locks_unsafe_for_binlog in MySQL 5.0), you must use row-based binary logging.

  • REPEATABLE READ

    This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use a unique index with a unique search condition lock only the index record found, not the gap before it. With other search conditions, these operations employ next-key locking, so they lock the index range scanned with gap locks or next-key (index record plug gap) locks and block insertions by other users to the gaps covered by the range.

    In consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 13.6.9.4, “Consistent Non-Locking Read”.

  • SERIALIZABLE

    This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE.


User Comments

Posted by Arjen Lentz on September 1 2008 11:02pm[Delete] [Edit]

Note! In MySQL 4.1, SET TRANSACTION ISOLATION LEVEL (the one that just selects the level for the *next* transaction, not session/global) persists for session.
A bug is listed for this, bug#39170
http://bugs.mysql.com/bug.php?id=39170

Add your own comment.