Phantom reads and how serializable prevents them

WHAT THIS TESTS The question checks whether you understand that phantoms are a range or predicate anomaly distinct from non-repeatable reads, and the concrete locking machinery that stops them.

A GOOD ANSWER COVERS The definition. A phantom read happens when a transaction runs a query with a search predicate, for example count all orders over 100 dollars, and later re-runs the same predicate within the same transaction and finds additional rows that another transaction inserted and committed in between. The set of matching rows changed, even though no row it previously read was modified.

WHY REPEATABLE READ CAN ALLOW IT Under classic lock-based Repeatable Read, a transaction takes shared locks on the rows it actually reads, preventing those rows from being changed, which stops non-repeatable reads. But it cannot lock rows that do not yet exist, so another transaction is free to insert a new row matching the predicate. That insert is the phantom.

HOW SERIALIZABLE PREVENTS IT Lock-based engines use range locks: next-key locking in InnoDB locks the index range and the gaps between keys, or more generally predicate locks cover the logical condition, so no conflicting insert can land in the range. MVCC-based serializable engines like Postgres instead use Serializable Snapshot Isolation, tracking read/write dependencies and aborting transactions whose interleaving could violate a serial order.

LIKELY FOLLOW-UPS How do gap and next-key locks work, why does Postgres Repeatable Read already block phantoms via snapshots, and what is the difference between SSI and two-phase locking.

ONE CONCRETE EXAMPLE Transaction A counts rows where status equals pending twice. Between the counts, transaction B inserts a new pending row and commits. Under classic Repeatable Read A sees a higher count the second time, a phantom; under Serializable with range locking, B's insert into that range blocks or A is aborted, so A's two counts agree.