Phantom Reads
A phantom read occurs when a transaction repeats a query and sees new rows inserted by another transaction, breaking a predicate-based decision.
Definition
A phantom read is an isolation anomaly in which a transaction executes the same query twice and the second execution returns rows that did not exist at the first execution, because another transaction inserted or committed matching rows. The new rows are phantoms, and the repeated result is no longer stable.
Context and scope
Phantoms matter when a transaction decides based on a set, not a single row: counting seats, checking whether a username exists, or summing ledger entries before posting. The danger is a decision that was valid at read time but invalid at commit time.
Common misconceptions
- Repeatable read always eliminates phantoms because previously read rows stay unchanged.
- Phantom reads only affect `SELECT *` queries, not aggregates or existence checks.
Anti-patterns
- Checking for duplicates with a SELECT and then inserting without a unique constraint.
- Relying on row-level locks alone to protect a predicate range.
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/transaction-iso.html
How it works
A transaction runs SELECT count(*) FROM bookings WHERE room_id = 7 AND day = '2026-11-02' and sees 0. Another transaction inserts a booking for the same room and day and commits. The first transaction reruns the count and sees 1. Under read committed, each statement gets a fresh snapshot, so this is expected. Under repeatable read, the count may remain 0 because the snapshot is fixed, but a subsequent update to the range can still conflict.
Databases implement protection differently. PostgreSQL uses snapshots; serializable adds predicate locking to detect dangerous read-write dependencies. MySQL InnoDB uses next-key locks for some index ranges under repeatable read, blocking inserts into the locked gap. A table without a suitable index may lock far more than intended.
Prevention
The strongest fix is a database constraint: a unique index on (room_id, day) makes the duplicate insert fail regardless of timing. When the rule cannot be expressed as a constraint, use a higher isolation level with retries, or lock a parent row that represents the resource. SELECT ... FOR UPDATE on the room row serializes the check-and-insert sequence. Advisory locks can work but are easy to misuse.
When to use this knowledge
Audit any check-then-act flow over a set: uniqueness checks, capacity checks, sequence allocation, and balance thresholds. Also audit reports that read twice within one transaction and expect identical results. Single-row updates protected by a version column do not face phantoms, but they may face lost updates.
Trade-offs and example
Serializable isolation prevents phantoms at the cost of aborts and retries. Gap locks prevent them but reduce concurrency and can deadlock. Unique constraints are precise and cheap but only cover rules expressible in the schema.
Example: two requests both check that username alice is free, then both insert. Without a unique index, both succeed and the login query later returns two users. The unique index turns the race into one failed insert, which the application can translate into a clear conflict response.