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 UPDATE on 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.

Recall

These cards are also in your review deck, scheduled with spaced repetition.