How MySQL Repeatable Read Solves Phantom Reads with MVCC and Next-Key Locks

Phantom read refers to a situation where a transaction executes two range queries and the second query returns newly inserted rows that were not present in the first. MySQL provides four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), and SERIALIZABLE. While SERIALIZABLE eliminates phantom reads, the article investigates whether REPEATABLE READ also solves the problem.

MVCC (Multi-Version Concurrency Control)

When a transaction starts in MySQL, it generates a ReadView that records the IDs of all currently active (uncommitted) transactions. This acts as a dividing line: transactions inside the active list are invisible, while committed transactions are visible. Data is stored as a version chain linked from newest to oldest. To decide which version is visible, MySQL compares the version's transaction ID against the ReadView's active list. If the newest version belongs to an active transaction (e.g., transaction 102 in the active list [102,103,104]), it is skipped; the next older version from a committed transaction (e.g., transaction 99) becomes visible. MVCC performs this real-time mathematical comparison rather than storing copies.

Snapshot Read Principle

Snapshot read is InnoDB's core mechanism for implementing MVCC. It reads a consistent snapshot of the database at the moment the reading transaction begins, not the latest version. The article illustrates a timeline:

Transaction A at t1 issues a query and generates a snapshot; no matching rows exist, so the result is empty.

Transaction B at t2 inserts a row with id=100 and commits.

Transaction A at t3 queries again; the result is still empty because the ReadView created at t1 is reused for the duration of Transaction A. This reuse acts like a shield that blocks all external changes, preventing phantom reads for snapshot reads.

Phantom Read Problem with Current Reads

Snapshot reads avoid phantom reads, but certain operations (e.g., UPDATE) must operate on the latest committed version to avoid overwriting another transaction's changes. The article presents a scenario:

At t2, Transaction B commits the row id=100. The row exists in the database but remains invisible to Transaction A due to MVCC.

Transaction A executes an UPDATE. Because UPDATE is a current read, it forces its way through the MVCC barrier and updates the invisible row (id=100). After the update, the row's transaction ID becomes Transaction A's own, making it visible to Transaction A (a transaction always sees its own modifications).

Transaction A then queries the table again and now sees the previously invisible row. The row count differs between the two queries — a phantom read has occurred.

Next-Key Lock

To eliminate phantom reads in current-read scenarios, MySQL introduces the Next-Key Lock , which combines a record lock and a gap lock. It physically prevents the insertion of new rows into the locked gap. When a SELECT ... FOR UPDATE is executed, the statement locks not only the matching record but also the surrounding gap (e.g., from id=99 to positive infinity). Any attempt to insert a new row within that gap (such as Transaction B trying to insert id=100) is blocked. Since the insert cannot proceed, the phantom read cannot materialize.

Summary

MVCC provides a consistent snapshot view with high performance, but mixing current reads (like UPDATE) can introduce phantom reads.

For data-modification operations, the Next-Key Lock mechanism ensures absolute safety by locking records and gaps.

Under the REPEATABLE READ isolation level, MySQL solves phantom reads through Next-Key Locks.