Phantom read
A phantom read is running the same range query twice inside one transaction and getting a changed matching set because another transaction committed an insert, deletion, or predicate-changing update. A changed value in a row already read is the related non-repeatable read. The existing illustration below uses an insert.
This is an illustrative timeline. The linked Scenario contains the executed setup and assertions.
The distinction matters because phantoms are about predicates, not rows: you can lock every row you read and still get phantoms, because the new row didn't exist to be locked. That's why the SQL standard treats them as a separate, harder anomaly (Adya's PMP, predicate-many-preceders), and why the standard's REPEATABLE READ is allowed to permit them.
Who prevents it
| Level | SQL standard | PostgreSQL | MySQL (InnoDB) |
|---|---|---|---|
| READ COMMITTED | permitted | happens (proof) | happens (proof) |
| REPEATABLE READ | permitted | prevented, stronger than the standard requires (proof) | prevented for plain SELECTs (proof); current reads see phantoms |
| SERIALIZABLE | prevented | prevented | prevented |
Both engines exclude later concurrent commits from REPEATABLE READ consistent SELECTs in one transaction, plus their own writes. This is not a frozen view for every operation. InnoDB current DML can reach post-snapshot rows; gap locks depend on the query, index, and level. PostgreSQL changing/locking commands have a changed-target conflict rule. The SERIALIZABLE table exclusions use contracts and marked derivations, not new executions here.
Related anomalies
- Non-repeatable read, the row-level version: same row, different data.
- Write skew, the predicate problem's evil twin on the write side: two transactions each check a predicate, then jointly falsify it.
See it happen
- PostgreSQL: Repeatable Read, one snapshot, no phantoms, and the
40001price tag - MySQL: Repeatable Read, the snapshot, the current reads that punch holes in it, and the predicate variant of read skew