Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 25.7Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 158.5Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

13.3.6 SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

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

Transaction Isolation Levels

To set the transaction isolation level, use an ISOLATION LEVEL level clause.

The default isolation level is REPEATABLE READ. Other permitted values are READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. For information about these isolation levels, see Section 14.10.2.1, “Transaction Isolation Levels”.

Transaction Isolation Level Scope

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

  • With the GLOBAL keyword:

    • The statement applies globally for all subsequent sessions.

    • Existing sessions are unaffected.

  • With the SESSION keyword:

    • The statement applies to all subsequent transactions performed within the current session.

    • The statement is permitted within transactions, but does not affect the current ongoing transaction.

    • If executed between transactions, the statement overrides any preceding statement that sets the next-transaction isolation level.

  • Without any SESSION or GLOBAL keyword:

    • The statement applies only to the next single transaction performed within the session.

    • Subsequent transactions revert to using the session isolation level.

    • The statement is not permitted within transactions:

      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

A change to the global 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 (prior to the start of that transaction).

To set the global isolation level at server startup, use the --transaction-isolation=level option 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 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 set or check the global and session transaction isolation levels at runtime by using the tx_isolation system variable:

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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.