Phantom Reads: When New Rows Appear Mid-Transaction

Why it exists

When multiple transactions run concurrently, one transaction might add new data that affects another's view of the database. A phantom read occurs when a transaction sees an inconsistent set of rows, which can lead to incorrect calculations or logical errors in multi-step operations that expect a stable dataset.

The mental model

Imagine you are counting all the blue cars on a street for a report. You walk the block and count 10. While you're walking back to your office, someone parks a new blue car. To double-check your numbers, you count again and find 11. That 11th car is a 'phantom'. Your query for 'blue cars' returned a different set of results within the same overall task. This is distinct from a non-repeatable read, which would be if one of the original 10 cars was repainted from blue to red.

How it works

A phantom read happens within a single transaction when it executes the same range query (a query with a WHERE clause) twice. Between these two executions, another transaction successfully commits an INSERT or DELETE that affects which rows match the first transaction's WHERE clause. The second time the query runs, it sees these 'phantom' rows that weren't there before, or it fails to see rows that have been deleted.

When to use it

You don't 'use' a phantom read; you prevent it. You must account for phantom reads in any transaction that requires a consistent view of a set of rows. This is critical for multi-step reporting jobs, financial calculations that sum up accounts matching certain criteria, or inventory checks that count items in a specific category. If the set of items cannot change mid-operation, you need an isolation level like Repeatable Read or Serializable.

When not to use it

For transactions where the exact set of rows doesn't need to be perfectly stable, a lower isolation level like Read Committed is often sufficient and more performant. If you are only reading a single row by its primary key, or if your application logic can tolerate a changing set of results between queries, the overhead of preventing phantoms may not be justified.

One canonical example

Transaction A starts at the 'Read Committed' isolation level. First, Transaction A runs SELECT COUNT(*) FROM employees WHERE salary > 100000; and gets the result 15. Then, Transaction B inserts a new highly-paid employee and commits: INSERT INTO employees (name, salary) VALUES ('Charlie', 120000); COMMIT; Finally, Transaction A, still running, executes its query again for a final report: SELECT COUNT(*) FROM employees WHERE salary > 100000;. This time, it gets the result 16. The new employee, Charlie, is the phantom row that appeared mid-transaction.