MySQL :: MySQL 8.4 Reference Manual :: 17.7.2.1 Transaction Isolation Levels (original) (raw)

REPEATABLE READ

This is the default isolation level forInnoDB.Consistent reads within the same transaction read thesnapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, theseSELECT statements are consistent also with respect to each other. SeeSection 17.7.2.3, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or FOR SHARE),UPDATE, andDELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

It is not recommended to mix locking statements (UPDATE,INSERT,DELETE, or SELECT ... FOR ...) with non-lockingSELECT statements in a singleREPEATABLE READ transaction, because typically in such cases you wantSERIALIZABLE. This is because a non-locking SELECT statement presents the state of the database from a read view which consists of transactions committed before the read view was created, and before the current transaction's own writes, while the locking statements use the most recent state of the database to use locking. In general, these two different table states are inconsistent with each other and difficult to parse.

READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, seeSection 17.7.2.3, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or FOR SHARE), 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 row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, seeSection 17.7.4, “Phantom Rows”.

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

Using READ COMMITTED has additional effects:

Consider the table created and populated like this:

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 17.6.2.1, “Clustered and Secondary Indexes”) rather than indexed columns.

Suppose that one session performs anUPDATE using these statements:

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

Suppose also that a second session performs anUPDATE by executing these statements following those of the first session:

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

As InnoDB executes eachUPDATE, it first acquires an exclusive lock for each row, 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 firstUPDATE acquires an x-lock on each row that it reads and does not release any of them:

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:

x-lock(1,2); block and wait for first UPDATE to commit or roll back

If READ COMMITTED is used instead, the first UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

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 theWHERE condition of theUPDATE:

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

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 firstUPDATE takes and retains an x-lock on each row where b = 2. The secondUPDATE blocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column b.

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

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

The READ COMMITTED isolation level can be set at startup or changed at runtime. At runtime, it can be set globally for all sessions, or individually per session.

READ UNCOMMITTED

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 adirty read. Otherwise, this isolation level works likeREAD COMMITTED.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE ifautocommit is disabled. Ifautocommit 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. (To force a plainSELECT to block if other transactions have modified the selected rows, disableautocommit.)

DML operations that read data from MySQL grant tables (through a join list or subquery) but do not modify them do not acquire read locks on the MySQL grant tables, regardless of the isolation level. For more information, seeGrant Table Concurrency.