Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

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:

  • With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.

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

  • Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level.

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';

Transaction Isolation Levels

For information about transaction isolation levels, see Section 14.8.2.1, “Transaction Isolation Levels”.


User Comments
Sign Up Login You must be logged in to post a comment.