Does MVCC Actually Prevent Phantom Reads? A Clear Explanation

MVCC solves phantom reads for snapshot reads

In InnoDB, a snapshot read creates a ReadView that records the list of active transactions at the moment of the first SELECT in a transaction. Under the REPEATABLE READ isolation level the same ReadView is reused for all subsequent SELECT statements, so the transaction always sees the data state as of its start. Rows inserted by other transactions after that point are invisible.

The classic definition of a phantom read is two identical range queries in the same transaction returning different row counts. Because the ReadView fixes the visible snapshot, newly inserted rows are not seen and the row count cannot change, so MVCC eliminates this form of phantom read.

Write‑conflict scenario often mislabeled as phantom read

-- Transaction A
BEGIN;
SELECT * FROM t WHERE name = 'test';  -- returns empty

-- Transaction B inserts a row with name='test' and commits

SELECT * FROM t WHERE name = 'test';  -- still empty (MVCC works)
INSERT INTO t (name) VALUES ('test');   -- error: duplicate key

The second SELECT shows the same empty result because the ReadView cannot see the row written by Transaction B. The subsequent INSERT fails because the unique index enforces a write‑time constraint. This situation is a write‑conflict, not a read‑consistency phantom read. MVCC governs what data a transaction can see; unique indexes govern whether a write is allowed.

Current‑read protection with gap locks

When a transaction performs a current read such as SELECT FOR UPDATE, UPDATE, or DELETE under REPEATABLE READ, InnoDB acquires gap locks on the index gaps surrounding the target rows. Gap locks block other transactions from inserting new rows into those gaps, thereby preventing phantom rows for current‑read operations.

Quick reference

Ordinary SELECT – snapshot read – no phantom rows – protected by MVCC ReadView.

SELECT FOR UPDATE – current read – no phantom rows – protected by gap lock.

UPDATE / DELETE – current read – no phantom rows – protected by gap lock.

Mixing snapshot and current reads

If a single transaction issues both a snapshot read and a later current read, the two statements may observe different data states. For example, an initial ordinary SELECT (snapshot read) will not see rows committed after the transaction started, while a subsequent UPDATE (current read) can see those newer rows because it acquires a gap lock and reads the latest committed version. Keeping the read type consistent within a transaction avoids such surprises.