.NET Architecture & System Design Interview Question #81

What is a phantom read?

Transactions, CQRS, Event Sourcing & Distributed Consistency Mid-Level Intermediate

Quick Interview Answer

A phantom read occurs when a transaction repeats a query and finds a different set of rows because another transaction inserted, deleted, or changed rows so they now match or stop matching the query.

Detailed Explanation

A phantom read occurs when a transaction repeats a query and finds a different set of rows because another transaction inserted, deleted, or changed rows so they now match or stop matching the query.

Serializable isolation is designed to prevent this class of anomaly, while behavior under snapshot-based isolation depends on the database implementation.

Code Example

Tx A: SELECT orders WHERE Total > 1000
      -> 5 rows

Tx B: INSERT matching order; COMMIT

Tx A: same query
      -> 6 rows