Phantom Read
A phantom read occurs when a transaction re-runs a query and finds new rows inserted by another transaction since its first execution.
Definition
A phantom read is a concurrency anomaly in which a transaction executes a query, another transaction inserts or deletes rows matching that query and commits, and the first transaction re-executes the query to find a different set of rows. It differs from a non-repeatable read, which concerns a changed row; a phantom concerns new or vanished rows matching a predicate. It is the anomaly that motivates serializable isolation and range locks.
Context and scope
Relevant when a transaction compares aggregates or enforces a rule over a set of rows that another transaction can add to, such as uniqueness checks and reporting totals.
Common misconceptions
- Phantom reads equal non-repeatable reads — one concerns inserted or deleted rows matching a predicate, the other a changed row
- Snapshot isolation eliminates phantoms — snapshot isolation alone can still allow write skew, in which transactions read overlapping sets and write disjoint rows
- Phantoms only matter to reporting — they also break application-level constraints such as "no two active bookings for the same slot"
Anti-patterns
- Checking a condition on a set and then inserting based on it, without a lock covering the range or an appropriate isolation level
- Computing a total and using it to make a decision while another transaction can add rows
- Assuming repeatable read prevents all predicate-based anomalies across every engine
Related concepts
Related examples
Authoritative source
What it is
A phantom read is what happens when the set of rows a query matches changes between two executions in the same transaction because another transaction inserted or deleted matching rows. The second execution returns rows that were not there the first time — the phantoms. It is a predicate-level anomaly: it is not about a single row changing value, but about the population qualifying for a condition.
Why it matters
Application rules frequently range over a set: no overlapping reservations, a unique active username, a total that must stay under a limit. Between the check and the action, another transaction can insert a qualifying row, so two transactions both believe they satisfy the rule and both commit, violating it. This is why phantom reads are the anomaly that serializable isolation is designed to eliminate, and why “check then insert” needs stronger protection than row-level guarantees.
How to use it well
When a decision depends on a set, make the check and the action one atomic unit under a level or lock that covers the range: serializable isolation, or an explicit range or predicate lock, or a unique constraint that the database enforces. Prefer declarative constraints to application checks where possible, because the engine can enforce them without a race. Recognise that snapshot isolation alone may still permit write skew, so read the guarantees your engine actually offers before assuming a level is enough.