Ghost Rows Appearing and Disappearing
A Phantom Read is subtler than a Non-Repeatable Read. It happens when you run a query that matches a set of rows, and when you run the same query again within the same transaction, the set has changed — rows have appeared or disappeared — because another transaction inserted or deleted rows that match your filter.
The "phantom" metaphor is apt: rows materialize or vanish as if by magic, even though you thought you had a stable view of the data.
Non-Repeatable Read vs. Phantom Read
| Anomaly | What Changes | Example |
|---|---|---|
| Non-Repeatable Read | A specific row's column values | price changes from $10 to $15 |
| Phantom Read | The set of rows matching a condition | A new row appears in WHERE salary > 100k |
How It Happens
Time Transaction A (Payroll Report) Transaction B (New Hire)
---- ---------------------------------------- ----------------------------------
T1 BEGIN
T2 SELECT * FROM employees WHERE salary > 100000
-- Returns: 5 rows. Sum = $600,000
T3 BEGIN
T4 INSERT INTO employees (name, salary)
VALUES ('Carol', 150000)
T5 COMMIT
T6 SELECT * FROM employees WHERE salary > 100000
-- Returns: 6 rows. Sum = $750,000 ← PHANTOM!
T7 -- Report shows inconsistent totals.
T8 COMMIT
This is especially dangerous for aggregate calculations. A payroll report might show a total that doesn't match the sum of its line items, depending on which query ran before or after Carol's insert.
The Fix
The Serializable isolation level prevents phantom reads. The SQL standard says Repeatable Read only needs to prevent non-repeatable reads, not phantoms. However, PostgreSQL's implementation of Repeatable Read (using MVCC snapshots) also prevents phantom reads in practice — a PostgreSQL-specific behavior that does not generalize to all databases (e.g., MySQL's InnoDB Repeatable Read does not prevent phantoms without gap locks).
A common interview question: "Does Repeatable Read prevent Phantom Reads?" The correct answer is: "It depends on the database." The SQL standard says no. PostgreSQL says yes (due to MVCC). MySQL's InnoDB uses gap locks to prevent phantoms at Repeatable Read. Always qualify your answer with the specific database engine.