Understanding MySQL InnoDB Transaction Isolation Levels, MVCC, and Lock Types

Most people know the ACID properties and the four isolation levels of database transactions, but few understand how each level is implemented.

This article uses MySQL InnoDB as an example to analyze how the default isolation level Repeatable Read (RR) is realized.

Key points covered: the four isolation levels, the problems each level addresses, MVCC, lock types, and case studies.

1. Isolation Levels

(1) Read Uncommitted : a transaction can read data modified by other uncommitted transactions.

(2) Read Committed : a transaction reads only data that other transactions have committed.

(3) Repeatable Read : a transaction sees the same data for repeated reads, regardless of other transactions' modifications.

(4) Serializable : transactions are executed serially, providing the highest isolation at the cost of concurrency.

2. Problems addressed by isolation levels

If isolation is ignored, concurrent transactions may cause:

Dirty read : Transaction A reads data written by Transaction B, which later rolls back.

Non‑repeatable read : Transaction A reads a row, Transaction B updates and commits it, then A reads the same row again and gets different data.

Phantom read : Re‑executing a range query returns a different set of rows because other transactions inserted or deleted rows in that range.

Non‑repeatable read concerns updates, phantom read concerns inserts/deletes. Resolving non‑repeatable reads requires row locks; resolving phantom reads requires gap locks.

3. MVCC (Multi‑Version Concurrency Control)

InnoDB implements Repeatable Read via MVCC. Each row has hidden fields for creation and deletion version numbers (transaction IDs).

Operations under the default Repeatable Read work as follows:

SELECT SELECT ... Reads rows whose creation version ≤ current transaction version and whose deletion version is NULL or > current version.

INSERT INSERT ... Stores the current transaction version as the row's creation version.

UPDATE UPDATE ... Creates a new version of the row with the current transaction version as its creation version and sets the old row's deletion version to the current transaction version.

DELETE DELETE ... Sets the row's deletion version to the current transaction version.

Snapshot read vs current read

Snapshot read returns the version visible at the start of the transaction; current read reads the latest version. Ordinary SELECTs are snapshot reads, while UPDATE, DELETE, INSERT, SELECT ... LOCK IN SHARE MODE, and SELECT ... FOR UPDATE are current reads.

When the isolation level is Repeatable Read, all ordinary SELECTs see the same snapshot, achieving repeatable reads. For current reads, InnoDB uses locking to also prevent phantom reads.

4. Lock Types

InnoDB defines three lock types:

Record Locks : lock individual index records (row locks).

Gap Locks : lock the gaps between index records.

Next‑Key Locks : a combination of record lock on the index record and gap lock on the preceding gap.

Example: for an index with values 10, 11, 13, 20, the next‑key locks cover (-∞,10], (10,11], (11,13], (13,20], (20,+∞).

5. Case Analysis

Assume a user table t_user with id (primary key), name (unique index), age (regular index), and address (no index).

Primary key index DELETE FROM t_user WHERE id=120; locks the clustered index row with id=120 (Record Lock). Other rows can still be inserted/updated.

Unique index DELETE FROM t_user WHERE name='n20'; locks the unique index record and the corresponding clustered row.

Regular index DELETE FROM t_user WHERE age=20; requires both record locks on matching index entries and gap locks to prevent phantom inserts.

Resulting locks:

Record Lock on age index entries (e.g., 20_120, 20_130).

Gap X‑Lock on intervals (10,20), (20,20), (20,40).

Record X‑Lock on clustered rows id=120/130.

No index DELETE FROM t_user WHERE address='a20'; cannot pinpoint rows, so InnoDB locks the entire clustered index and gaps, effectively a table‑level lock.

Conclusion

In READ COMMITTED, InnoDB only acquires Record Locks on the accessed rows, not Gap locks, allowing phantom reads. In REPEATABLE READ, InnoDB uses Next‑Key Locks (Record + Gap) to prevent phantom reads. However, to guarantee repeatable reads without phantom reads, applications must use locking reads.