How MySQL Prevents Phantom Reads: MVCC and Next‑Key Locks Explained

1. Conclusion

Under the REPEATABLE READ isolation level, InnoDB solves phantom reads by combining MVCC (which handles snapshot reads) with next‑key locks (which handle current reads).

2. What Is a Phantom Read?

Consider two transactions: update table set name="hh" where id>3; Transaction A executes the update. Then Transaction B inserts a new row and commits:

insert into table values(11, "uu");
commit;

When Transaction A subsequently runs: select * from table where id>3 the result includes the newly inserted row (id 11, uu), demonstrating a phantom read. The effect is caused by the committed changes of Transaction B becoming visible to Transaction A.

Phantom reads differ from non‑repeatable reads: the former affect a range of rows, the latter affect a single row.

3. How Is It Solved?

3.1 Current Read (Locking Read)

Current reads (SELECT … FOR UPDATE/S, UPDATE, DELETE) acquire next‑key locks that lock the target row and the surrounding index interval. In the example, the statement select * from table where id>3 locks the row with id=3 and the range id>3, preventing other transactions from inserting rows into that range and thus avoiding phantom rows.

3.2 Ordinary Read (Snapshot Read)

Ordinary reads do not acquire locks; they rely on MVCC. Each row stores a creation version and a deletion version . Every transaction receives a monotonically increasing version number at start.

During REPEATABLE READ, the visibility rules are:

Read rows whose creation version ≤ current transaction version.

Read rows whose deletion version is NULL or > current transaction version.

These rules are applied to each DML operation:

SELECT

Read rows satisfying the two version conditions.

INSERT

Set the row’s creation version to the current transaction version.

UPDATE

Insert a new version of the row with the current transaction version as its creation version, and set the old row’s deletion version to the current transaction version.

DELETE

Set the row’s deletion version to the current transaction version.

Diagrams illustrating version handling:

Key point: a read must satisfy both conditions—creation version ≤ current transaction version and (deletion version is NULL or > current transaction version). This ensures that rows inserted by later transactions (like Transaction B) are invisible to the earlier transaction, eliminating phantom reads.

4. Additional Note

In READ COMMITTED mode, MVCC cannot prevent phantom reads or non‑repeatable reads because each read sees the latest committed snapshot, which may change after each statement.

Other References

https://segmentfault.com/a/1190000012669504