A month-end report runs inside one transaction: it counts the open invoices, then sums their amounts. Under ANSI SQL isolation levels, new invoices inserted between the two steps make the count and sum disagree. Which isolation level prevents this, and at what cost?
Select an answer to reveal the explanation.
Short Explanation
New rows popping into a range you already read are phantoms, like guests sneaking into a party after you counted heads. Only SERIALIZABLE in the ANSI model stops phantoms, and you pay for it in concurrency. The trap is REPEATABLE READ: it guards the rows you saw, not the gaps where new rows land.
Full Explanation
A phantom read happens when a transaction re-executes a query over a range of rows and finds rows that another transaction inserted and committed in between. The month-end report reads the same predicate twice, so a phantom causes the count and the sum to describe different sets. The ANSI SQL standard defines SERIALIZABLE as the level that prevents dirty reads, nonrepeatable reads and phantoms; engines implement it with key-range locks or with optimistic validation that aborts conflicting transactions. The cost is reduced concurrency: inserts into the protected range wait or fail until the report finishes. REPEATABLE READ prevents changes to rows already read but, by the standard definition, still allows new rows that satisfy the predicate to appear. READ COMMITTED gives each statement its own view of committed data, so the second statement can see invoices the first did not. READ UNCOMMITTED adds dirty reads on top of phantoms and makes the mismatch worse. Exam caveat: snapshot isolation can also give a consistent view for a read-only report, and some engines strengthen REPEATABLE READ beyond the standard, so know the ANSI definitions. Operational check: schedule the report outside peak insert windows and monitor blocking and aborted transactions while it runs.