SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
This statement sets the transaction 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
orGLOBAL
keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session.
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=
option to mysqld on the command line or in an
option file. Values of level
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
To determine the global and session transaction isolation levels
at runtime, check the value of the
tx_isolation
system variable:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
InnoDB
supports each of the transaction
isolation levels described here using different locking
strategies. The default level is
REPEATABLE READ
. For additional
information about InnoDB
record-level locks and
how it uses them to execute various types of statements, see
Section 13.6.9.4, “InnoDB
Record, Gap, and Next-Key Locks”, and
Section 13.6.9.6, “Locks Set by Different SQL Statements in InnoDB
”.
The following list describes how MySQL supports the different transaction levels:
SELECT
statements are performed in a nonlocking 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 likeREAD COMMITTED
.A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 13.6.9.2, “Consistent Nonlocking Reads”.
For locking reads (
SELECT
withFOR UPDATE
orLOCK IN SHARE MODE
),InnoDB
locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. ForUPDATE
andDELETE
statements, locking depends on whether the statement uses a unique index with a unique search condition (such asWHERE id = 100
), or a range-type search condition (such asWHERE id > 100
). For a unique index with a unique search condition,InnoDB
locks only the index record found, not the gap before it. For range-type searches,InnoDB
locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.NoteIn MySQL 5.5, if the
READ COMMITTED
isolation level is used or theinnodb_locks_unsafe_for_binlog
system variable is enabled, there is noInnoDB
gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated theWHERE
condition.If you use
READ COMMITTED
or enableinnodb_locks_unsafe_for_binlog
, you must use row-based binary logging.This is the default isolation level for
InnoDB
. For consistent reads, there is an important difference from theREAD COMMITTED
isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking)SELECT
statements within the same transaction, theseSELECT
statements are consistent also with respect to each other. See Section 13.6.9.2, “Consistent Nonlocking Reads”.For locking reads (
SELECT
withFOR UPDATE
orLOCK IN SHARE MODE
),UPDATE
, andDELETE
statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition,InnoDB
locks only the index record found, not the gap before it. For other search conditions,InnoDB
locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.This level is like
REPEATABLE READ
, butInnoDB
implicitly converts all plainSELECT
statements toSELECT ... LOCK IN SHARE MODE
if autocommit is disabled. If autocommit is enabled, theSELECT
is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (This means that to force a plainSELECT
to block if other transactions have modified the selected rows, you should disable autocommit.)