Can InnoDB’s REPEATABLE READ Truly Prevent Phantom Reads? A Deep Dive

What Is a Phantom Read?

A phantom read occurs when a transaction executes a range query (SELECT) and another concurrent transaction inserts a new row that satisfies the range, causing the result set size to change between reads.

MVCC and Phantom Reads

MVCC (Multiversion Concurrency Control) provides two types of reads in InnoDB: snapshot reads (the default, non‑locking SELECT) and current reads (locking SELECT, UPDATE, DELETE, etc.). Snapshot reads see the data as of the moment the snapshot is taken, while current reads see the latest committed data.

SELECT * FROM xx_table WHERE ...

In RC, each SELECT generates a new snapshot, so it always reads the latest version. In RR, the snapshot is created on the first SELECT of the transaction and remains unchanged unless the transaction itself modifies data.

Gap Locks and Phantom Reads

When a transaction uses SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, InnoDB acquires not only row locks but also gap locks on the spaces between rows. These gap locks block other transactions from inserting new rows into the locked range, thereby preventing phantom reads.

SELECT * FROM xx_table LOCK IN SHARE MODE;

SELECT * FROM xx_table FOR UPDATE;

Phantom Reads That MVCC Cannot Solve

MVCC only protects snapshot reads. If a transaction performs a current read (e.g., SELECT ... FOR UPDATE) after another transaction has inserted a row, the current read will see the new row, resulting in a phantom read. This also applies to UPDATE, DELETE, and other statements that acquire current reads.

How to Avoid Phantom Reads

To completely eliminate phantom reads, use the SERIALIZABLE isolation level. If you want to avoid most phantom reads while staying in REPEATABLE READ, follow these practices:

Prefer snapshot reads (plain SELECT) for queries that do not need to lock rows.

If locking is required, acquire the lock at the beginning of the transaction so that gap locks are in place before any inserts can occur.

Be aware that gap locks can increase the risk of deadlocks, so use them judiciously.

Summary

In InnoDB, REPEATABLE READ combined with MVCC solves phantom reads for snapshot (non‑locking) queries, but current reads can still encounter phantom rows. Using gap locks via SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE prevents inserts into the locked range, thereby avoiding phantom reads. For absolute safety, switch to the SERIALIZABLE isolation level.