14.7.2.1 Transaction Isolation Levels

Transaction isolation is one of the foundations of database
processing. Isolation is the I in the acronym
ACID; the isolation level is
the setting that fine-tunes the balance between performance and
reliability, consistency, and reproducibility of results when
multiple transactions are making changes and performing queries
at the same time.

A user can change the isolation level for a single session or
for all subsequent connections with the SET
TRANSACTION statement. To set the server's default
isolation level for all connections, use the
--transaction-isolation option on
the command line or in an option file. For detailed information
about isolation levels and level-setting syntax, see
Section 13.3.6, “SET TRANSACTION Syntax”.

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.

The following list describes how MySQL supports the different
transaction levels. The list goes from the most commonly used
level to the least used.

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 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. Gap locking
is only used for foreign-key constraint checking and
duplicate-key checking.

Because gap locking is disabled, phantom problems may occur,
as other sessions can insert new rows into the gaps. For
information about phantoms, see
Section 14.7.4, “Phantom Rows”.

Only row-based binary logging is supported with the
READ COMMITTED isolation level. If you
use READ COMMITTED with
binlog_format=MIXED, the
server automatically uses row-based logging.

Using READ COMMITTED has additional
effects:

For UPDATE or
DELETE statements,
InnoDB holds locks only for rows that
it updates or deletes. Record locks for nonmatching rows
are released after MySQL has evaluated the
WHERE condition. This greatly reduces
the probability of deadlocks, but they can still happen.

For UPDATE statements, if
a row is already locked, InnoDB
performs a “semi-consistent” read,
returning the latest committed version to MySQL so that
MySQL can determine whether the row matches the
WHERE condition of the
UPDATE. If the row
matches (must be updated), MySQL reads the row again and
this time InnoDB either locks it or
waits for a lock on it.

Suppose also that a second session performs an
UPDATE by executing this
statement following those of the first session:

# Session B
UPDATE t SET b = 4 WHERE b = 2;

As InnoDB executes each
UPDATE, it first acquires an
exclusive lock for each row that it reads, and then
determines whether to modify it. If
InnoDB does not modify the row,
it releases the lock. Otherwise,
InnoDB retains the lock until
the end of the transaction. This affects transaction
processing as follows.

When using the default REPEATABLE READ
isolation level, the first
UPDATE acquires an x-lock on
each row that it reads and does not release any of them:

For the second UPDATE,
InnoDB does a
“semi-consistent” read, returning the latest
committed version of each row that it reads to MySQL so that
MySQL can determine whether the row matches the
WHERE condition of the
UPDATE:

However, if the WHERE condition includes
an indexed column, and InnoDB uses the
index, only the indexed column is considered when taking and
retaining record locks. In the following example, the first
UPDATE takes and retains an
x-lock on each row where b = 2. The second
UPDATE blocks when it tries
to acquire x-locks on the same records, as it also uses the
index defined on column b.

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.

SERIALIZABLE

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.)