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.
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.
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 levellevel 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';
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.3.9, “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.3.9.4, “InnoDB Record, Gap, and Next-Key Locks” and
Section 14.3.9.6, “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.
This is the default isolation level for
InnoDB. For consistent reads, there is an
important difference from the READ
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, these SELECT
statements are consistent also with respect to each other. See
Section 14.3.9.2, “Consistent Nonlocking Reads”.
For locking reads (SELECT with
FOR UPDATE or LOCK IN SHARE
MODE), UPDATE, and
DELETE 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.
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 14.3.9.2, “Consistent Nonlocking Reads”.
For locking reads (SELECT with
FOR UPDATE or LOCK IN SHARE
MODE),
UPDATE statements,
and DELETE statements,
InnoDB locks only index records, not the
gaps before them, and thus permits the free insertion of new
records next to locked records.
In MySQL 5.5, when 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 nonmatching rows are released after MySQL has
evaluated the WHERE condition.
If you use READ COMMITTED or enable
innodb_locks_unsafe_for_binlog,
you must use row-based binary logging.
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 like
READ COMMITTED.
This level is like REPEATABLE
READ, but InnoDB implicitly
converts all plain SELECT
statements to SELECT
... LOCK IN SHARE MODE if autocommit is disabled. If
autocommit is enabled, the
SELECT 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. (To force a plain
SELECT to block if other
transactions have modified the selected rows, disable
autocommit.)