Databases & SQL Practice Question 7: Phantom Read Isolation
Databases & SQL · Test 1 · question 7 of 10
Transaction T1 runs at READ COMMITTED and executes SELECT COUNT(*) FROM orders WHERE status = 'pending';, which returns 12. While T1 is still open, transaction T2 inserts a new order with status = 'pending' and commits. T1 runs the same query again and now gets 13. No existing row was changed.
Using the SQL standard's definitions, which anomaly did T1 experience, and what is the lowest isolation level at which the standard guarantees it cannot occur?
Show answer and explanation
Answer:
A phantom read happens when a transaction re-runs a query with a search condition and the *set of rows* that match has changed because another transaction committed. Here a new row appeared. The SQL standard allows phantom reads at READ UNCOMMITTED, READ COMMITTED and REPEATABLE READ, and forbids them only at SERIALIZABLE.
Engines are allowed to give stronger guarantees than the standard requires. PostgreSQL's REPEATABLE READ uses a transaction-wide snapshot and does not let phantoms happen. The question asks about the standard's guarantee, though, and that is SERIALIZABLE.
Option A is wrong because a dirty read means seeing data that another transaction has not yet committed. T2 had committed before T1's second query, and READ COMMITTED already prevents dirty reads, which is why the first count was not affected.
Option B describes a different anomaly. A non-repeatable read is when a row you already read comes back with modified values. The stem says no existing row changed. A new row entered the result set, and that is the defining feature of a phantom.
Option C names the right anomaly but the wrong level. By the standard, REPEATABLE READ stops non-repeatable reads but still allows phantoms. That it happens to be true in PostgreSQL does not make it the standard's guarantee.
Reference: PostgreSQL: Transaction Isolation