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
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.
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 allowable 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 translation
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.8.4, “InnoDB
Record, Gap, and Next-Key Locks”, and
Section 13.6.8.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 like
READ 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.8.2, “Consistent Nonlocking Reads”.
For locking reads (SELECT
with
FOR UPDATE
or LOCK IN SHARE
MODE
), InnoDB
locks only index
records, not the gaps before them, and thus allows the free
insertion of new records next to locked records. For
UPDATE
and
DELETE
statements, locking
depends on whether the statement uses a unique index with a
unique search condition (such as WHERE id =
100
), or a range-type search condition (such as
WHERE 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.
In MySQL 5.5, 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 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.
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 13.6.8.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.
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. (This means that to force a plain
SELECT
to block if other
transactions have modified the selected rows, you should
disable autocommit.)
User Comments
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.