A phantom read is a concurrency anomaly in which a transaction runs the same query twice and gets a different set of rows, because another transaction has inserted or deleted rows matching the query’s condition and committed in between. In database transactions, phantoms concern sets of rows defined by a predicate, such as all orders for a customer, rather than changes to rows the transaction has already read.
The SQL standard lists the phantom read as one of three phenomena that isolation levels are defined to prevent, alongside the dirty read and the non-repeatable read. Only the SERIALIZABLE level must exclude all three under the standard.
How a phantom read happens
Consider transaction T1 running SELECT count(*) FROM bookings WHERE room = 12 AND day = '2026-12-01' and getting zero. Transaction T2 then inserts a booking for that room and day and commits. When T1 repeats the query, it counts one row. No row T1 read was modified; instead, a new row appeared within the range T1 had queried, which is the defining feature of a phantom.
Ordinary row locks cannot stop this, because the phantom row did not exist when T1 read the range, so there was nothing to lock. Preventing phantoms requires protecting the predicate or key range itself, either with range locks or by giving the transaction a consistent snapshot and validating that its reads remain correct.
Phantoms also occur in the opposite direction: a row that matched the first query can vanish from the second because another transaction deleted it or updated it so that it no longer satisfies the predicate. The effect is not limited to SELECT statements that return rows. Aggregates such as COUNT and SUM, existence checks with EXISTS, and UPDATE or DELETE statements that select rows by a condition can all be affected, since each of them evaluates a predicate over a set that may change between statements.
Phantom reads among transaction anomalies
Phantoms are often confused with non-repeatable reads. The difference lies in what changes between the two reads: an existing row’s values, or membership in the result set.
| Anomaly | What the transaction observes | Cause |
|---|---|---|
| Dirty read | Data from an uncommitted transaction | Reading writes that may roll back |
| Non-repeatable read | Changed values in a row read earlier | Another transaction updated or deleted it |
| Phantom read | Rows added to or removed from a result set | Another transaction inserted or deleted matching rows |
| Write skew | Two transactions break a joint rule | Each acts on a snapshot the other changed |
Write skew is closely related. Two transactions each check a condition over a set of rows, find it satisfied, and insert or update rows that together violate it. Many write-skew cases are phantom problems in disguise, because the conflicting rows did not exist when the check ran.
Isolation levels and phantom protection
The standard permits phantoms at READ UNCOMMITTED, READ COMMITTED and REPEATABLE READ. Real systems often provide stronger guarantees than the minimum, which makes the behavior of a given isolation level dependent on the product.
| Isolation level | SQL standard | Common implementation behavior |
|---|---|---|
| READ UNCOMMITTED | Phantoms allowed | Phantoms possible |
| READ COMMITTED | Phantoms allowed | Each statement sees new commits, phantoms possible |
| REPEATABLE READ | Phantoms allowed | PostgreSQL snapshot prevents them for plain reads; InnoDB prevents them for consistent reads |
| SNAPSHOT (SQL Server) | Not a standard level | No phantoms in reads, write skew still possible |
| SERIALIZABLE | Phantoms prohibited | Prevented by range locks or serializable snapshot isolation |
Under MVCC snapshot isolation, a transaction reads from a fixed snapshot, so repeating a plain SELECT returns the same rows and the classic phantom read disappears. This does not make the transaction serializable. Decisions based on the snapshot can still conflict with concurrent inserts, which is why a booking check at REPEATABLE READ in PostgreSQL can still produce a double booking through write skew.
Mechanisms that prevent phantoms
Databases use two broad families of techniques. Pessimistic approaches lock the range a query touched so that conflicting inserts wait. Optimistic approaches let transactions run and abort one of them at commit if their reads and writes cannot be ordered serially.
| Technique | Used by | Trade-off |
|---|---|---|
| Next-key and gap locks | MySQL InnoDB for locking reads and SERIALIZABLE | Blocking, risk of deadlocks |
| Key-range locks | SQL Server at SERIALIZABLE | Blocking on index ranges |
| Predicate locks with SSI | PostgreSQL at SERIALIZABLE | No blocking, but serialization failures to retry |
| Unique or exclusion constraints | Most relational databases | Precise, but only for expressible rules |
| Explicit lock on a parent row | Application design | Simple, serializes related work |
Range locking depends on suitable indexes. In InnoDB, gap locks are taken on the index used to find rows; if the condition cannot use an index, the engine may lock far more of the table than necessary. With serializable snapshot isolation, applications must be ready to catch serialization failures, SQLSTATE 40001, and retry the whole transaction.
Practical guidance
Phantoms matter whenever a transaction checks for the absence or count of rows and then acts on the result: preventing double bookings, enforcing a maximum number of seats, assigning the next free number or checking a total before a withdrawal. Pure reporting queries rarely need protection beyond a consistent snapshot.
- Prefer a declarative constraint when the rule fits one, such as a unique index or an exclusion constraint on time ranges.
- Use SERIALIZABLE for transactions with check-then-act logic that constraints cannot express, together with a retry loop.
- Alternatively, lock a common parent row, such as the room or event, with
SELECT ... FOR UPDATEbefore checking. - Do not assume REPEATABLE READ means the same thing in every database; test the behavior on the product in use.
A plain-language summary
Imagine checking a guest list and seeing that a table at a restaurant is free. While you write down the reservation, someone else books the same table and saves it first. When you look at the list again, a reservation has appeared that was not there before. That surprise entry is a phantom.
Databases offer settings and rules that stop such entries from sneaking in while a check is in progress. Stricter settings are safer but can make some work wait or retry.
An example from practice
A conference platform lets attendees register for workshops limited to 30 seats. The registration code counts existing registrations for the workshop and inserts a new row if the count is below 30. It runs at READ COMMITTED in PostgreSQL. During a popular launch, dozens of requests arrive within the same second, several transactions all count 29 seats taken, and each inserts a registration. The workshop ends up with 34 attendees.
The team considers SERIALIZABLE with retries, but chooses a simpler fix: each registration first locks the workshop row with SELECT ... FOR UPDATE, then counts and inserts. Concurrent registrations for the same workshop now wait for one another, while different workshops are unaffected. Load tests with hundreds of parallel requests confirm that no workshop exceeds its limit.