14.2.10.3. InnoDB and TRANSACTION ISOLATION LEVEL
In terms of the SQL:1992 transaction isolation levels, the
InnoDB default is REPEATABLE
READ. InnoDB offers all four
transaction isolation levels described by the SQL standard. You
can set the default isolation level for all connections by using
the --transaction-isolation option on the
command line or in an option file. For example, you can set the
option in the [mysqld] section of an option
file like this:
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
A user can change the isolation level for a single session or
for all new incoming connections with the SET
TRANSACTION statement. Its syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
Note that there are hyphens in the level names for the
--transaction-isolation option, but not for the
SET TRANSACTION statement.
The default behavior is to set the isolation level for the next
(not started) transaction. If you use the
GLOBAL keyword, the statement sets the
default transaction level globally for all new connections
created from that point on (but not for existing connections).
You need the SUPER privilege to do this.
Using the SESSION keyword sets the default
transaction level for all future transactions performed on the
current connection.
Any client is free to change the session isolation level (even
in the middle of a transaction), or the isolation level for the
next transaction.
You can determine the global and session transaction isolation
levels by checking the value of the
tx_isolation system variable with these
statements:
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
In row-level locking, InnoDB uses next-key
locking. That means that besides index records,
InnoDB can also lock the “gap”
preceding an index record to block insertions by other users
immediately before the index record. A next-key lock refers to a
lock that locks an index record and the gap before it. A gap
lock refers to a lock that only locks a gap before some index
record.
A detailed description of each isolation level in
InnoDB follows:
-
READ UNCOMMITTED
SELECT statements are performed in a
non-locking fashion, but a possible earlier version of a
record 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.
-
READ COMMITTED
A somewhat Oracle-like isolation level. All SELECT
... FOR UPDATE and SELECT ... LOCK IN
SHARE MODE statements lock only the index records,
not the gaps before them, and thus allow the free insertion
of new records next to locked records.
UPDATE and DELETE
statements using a unique index with a unique search
condition lock only the index record found, not the gap
before it. In range-type UPDATE and
DELETE statements,
InnoDB must set next-key or gap locks and
block insertions by other users to the gaps covered by the
range. This is necessary because “phantom rows”
must be blocked for MySQL replication and recovery to work.
Consistent reads behave as in Oracle: Each consistent read,
even within the same transaction, sets and reads its own
fresh snapshot. See
Section 14.2.10.4, “Consistent Non-Locking Read”.
-
REPEATABLE READ
This is the default isolation level of
InnoDB. SELECT ... FOR
UPDATE, SELECT ... LOCK IN SHARE
MODE, UPDATE, and
DELETE statements that use a unique index
with a unique search condition lock only the index record
found, not the gap before it. With other search conditions,
these operations employ next-key locking, locking the index
range scanned with next-key or gap locks, and block new
insertions by other users.
In consistent reads, there is an important difference from
the READ COMMITTED isolation level: All
consistent reads within the same transaction read the same
snapshot established by the first read. This convention
means that if you issue several plain
SELECT statements within the same
transaction, these SELECT statements are
consistent also with respect to each other. See
Section 14.2.10.4, “Consistent Non-Locking Read”.
-
SERIALIZABLE
This level is like REPEATABLE READ, but
InnoDB implicitly commits all plain
SELECT statements to SELECT ...
LOCK IN SHARE MODE.
