Understanding Phantom Reads and Isolation Levels in ANSI SQL and MySQL

The article begins by defining phantom reads as the phenomenon where a transaction T1 reads a set of rows satisfying a search condition, another transaction T2 inserts or updates rows that now satisfy the same condition, and T1’s subsequent read returns a different set of rows.

It contrasts phantom reads (P3) with non‑repeatable reads (P2), highlighting that P3 involves creation of new rows while P2 involves modification or deletion of existing rows, and that phantom reads increase the result‑set size whereas non‑repeatable reads keep the row count unchanged.

ANSI SQL‑92 maps isolation levels to prohibited anomalies: read‑uncommitted ↔ P1 (dirty read), read‑committed ↔ P2 (non‑repeatable read), repeatable‑read ↔ P3 (phantom read), and serializable has no mapped anomaly. The article cites the paper A Critique of ANSI SQL Isolation Levels to argue that the ANSI definition of repeatable‑read is misleading because it does not actually prevent phantom reads.

Lock‑based isolation levels are introduced: a locking repeatable‑read holds long‑duration read locks on individual rows and short‑duration predicate locks on the search range. In MySQL this corresponds to record locks plus next‑key (gap) locks, which block inserts into the gap and therefore prevent phantom reads.

The article answers several concrete questions:

Why does ANSI repeatable‑read prevent non‑repeatable reads but allow phantom reads? Because the ANSI definition only locks the rows read, not the predicate range.

Is MySQL’s repeatable‑read based on locks? Yes, it combines row‑level locks (for current reads) with MVCC snapshot reads; the current‑read mode acquires next‑key locks that block phantom inserts.

Does MySQL repeatable‑read allow phantom reads? In strict terms, no; the next‑key lock prevents the insertion of new rows that would satisfy the predicate, and snapshot reads alone cannot see phantom rows because they read a consistent snapshot.

Can a “loose” definition of phantom read occur under MySQL? Only if snapshot reads are mixed with current reads, but even then MVCC ensures repeatable‑read semantics, so true anomalies are not observed.

Code examples illustrate the transaction sequences:

// T1
begin;
Q1: select a from t where a>1 and a<5;
Q2: select a from t where a>1 and a<5;

Between Q1 and Q2, T2 may execute one of the following statements, each producing different phenomena:

// T2 examples
insert into t(a) values(2);               // phantom (P3)
update t set a=3 where a=6;               // phantom (P3)
update t set a=2 where a>1 and a<5;       // non‑repeatable (P2)
delete from t where a>1 and a<5;          // non‑repeatable (P2)

Further discussion covers the “relaxed” phantom definition (H3) from the cited paper and explains why MySQL’s locking strategy still prevents the observable effects of such anomalies.

References are provided for MySQL next‑key locking documentation and the original critique paper.