What is phantom read?

Phantom read is an anomaly in concurrent transaction execution in databases. It occurs when, within a single transaction, executing the same query with a WHERE condition multiple times returns a different set of rows. This happens because another transaction has added or removed rows matching the condition between the two executions of the same query in the first transaction.

This differs from non-repeatable read, where the values of existing rows are changed. In phantom read, the set of rows changes.

Example:

1. Transaction A executes SELECT COUNT(*) FROM accounts WHERE status = 'active', getting 100.
2. Transaction B adds 5 new accounts records with status = 'active'.
3. Transaction A executes SELECT COUNT(*) FROM accounts WHERE status = 'active' again, getting 105. — This is a phantom read.

Database management systems use transaction isolation levels to prevent such anomalies. The SERIALIZABLE isolation level prevents phantom reads, ensuring that concurrent transaction execution is equivalent to some serial execution of transactions. Lower isolation levels, such as REPEATABLE READ, can prevent non-repeatable reads but not phantom reads.