Notestransactions🌱 seedling
Phantom Read
Also: Phantom · Phantoms · Phantom Problem · Materializing Conflicts · Predicate Conflict
A phantom is a write in one transaction that changes the result of another transaction's search, typically an insert into a range someone else just queried. Row locks can't stop it, because the row didn't exist when the search ran.
Why it exists
Checks about absence are everywhere: "is this username free?", "does any booking overlap?", "are fewer than 100 tickets sold?". Two transactions can both see "no" and both insert.
How it works
- Phantom read (the ANSI sense): rerunning the same query in a transaction returns new rows. Snapshot Isolation prevents this; Postgres Repeatable Read does.
- Phantom-driven Write Skew: each transaction inserts a row the other's search would have matched. Snapshot isolation allows this.
- Predicate and index-range locks: Two-Phase Locking locks the search condition (or the index range it scanned), so inserts into it wait. InnoDB's next-key and gap locks do this for locking reads.
- SSI tracks reads of index ranges and aborts on a dangerous cycle.
Trade-offs
| Fix | Cost |
|---|---|
| unique / exclusion constraint | only for "no duplicates" or "no overlaps" |
| materialize the conflict (lock a row per show, slot or seat) | schema distorted for concurrency |
| Serializable | aborts or range-lock blocking |
In practice
- Postgres exclusion constraints prevent overlapping bookings:
EXCLUDE USING gist (room_id WITH =, during WITH &&). - ACIDRain (2017) found inventory and voucher races in 12 popular e-commerce platforms, many of them check-then-insert patterns.
Pitfalls
- Reading "prevents phantom reads" in docs as "prevents phantoms". Postgres Repeatable Read prevents the first, not phantom-driven write skew.
SELECT … FOR UPDATEon an empty result locks nothing.- Materializing the conflict under Postgres Repeatable Read: the snapshot predates the lock, so a count after it can be stale. Update the locked row (e.g. a sold counter) so the second writer gets a serialization failure, or use Read Committed.
Connections
- Part of
- Isolation Level
- Learn first
- Write Skew
- Mentioned
- Snapshot Isolation
Recall
These cards are also in your review deck, scheduled with spaced repetition.