Phantom read
A phantom read is running the same range query twice inside one transaction and getting new rows. No row you already saw has changed — that would be a non-repeatable read — instead, rows that match your WHERE clause appear out of nowhere, inserted and committed by someone else between your two reads.
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 beat the standard here: a per-transaction snapshot freezes the whole database, predicates included, so plain SELECTs at REPEATABLE READ are phantom-free on both. The engines' fine print differs — MySQL's writes and locking reads bypass the snapshot and do see phantoms (InnoDB's gap locks exist to control what those current reads meet), while PostgreSQL keeps one view for everything and instead aborts stale writes.
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