When multiple transactions run concurrently against a database, maintaining correctness becomes surprisingly subtle. While most engineers understand dirty reads and non-repeatable reads, phantom reads represent a more elusive class of isolation anomaly that shaped how modern databases balance consistency and performance.

What Are Phantom Reads?

A phantom read occurs when a transaction executes the same query twice and gets different sets of rows, not because existing rows changed, but because other transactions inserted or deleted rows that match the query predicate.

Consider a transaction that counts all orders over $1000, performs some validation logic, then counts again. If another transaction inserts a new $1500 order between those two counts, the second query returns a different result set—a “phantom” row appeared. The data the first transaction read didn’t change; the set of data matching its criteria expanded.

This differs from a non-repeatable read, where the same row returns different values across reads within a transaction. Phantoms are about the membership of result sets, not the values within known rows.

Why Traditional Locking Falls Short

Standard row-level locks prevent dirty reads and non-repeatable reads effectively. If transaction A locks row X, transaction B cannot modify it until A commits. But phantoms involve rows that don’t exist yet when the first query runs.

Serializable isolation—the strongest standard level—prevents phantoms by using predicate locks or range locks. These lock not just existing rows but the logical space where matching rows could appear. The cost is substantial: every transaction that might insert an order over $1000 must check whether any concurrent transaction has locked that predicate range. This coordination overhead degrades throughput significantly under high concurrency.

Two-phase locking implementations often fall back to table-level locks for predicate queries, sacrificing parallelism entirely. Index range locks improve granularity but still require careful coordination and can create complex deadlock scenarios.

Snapshot Isolation as the Practical Answer

Most production databases today default to snapshot isolation or a close variant. PostgreSQL calls it Repeatable Read but implements full snapshot isolation. Oracle and SQL Server offer it explicitly. MySQL’s InnoDB uses it for REPEATABLE READ.

Snapshot isolation gives each transaction a consistent view of the database as it existed when the transaction started. Reads never block writes and writes never block reads—transactions operate on private snapshots. The database tracks which data versions are visible to each transaction using multi-version concurrency control (MVCC).

Under snapshot isolation, phantom reads cannot occur within the same transaction’s view. The count query will return the same result set both times because the transaction sees a frozen snapshot that excludes any orders inserted after it began. This provides intuitive semantics without predicate locking overhead.

The tradeoff is write skew: two transactions can both read overlapping data, make decisions based on what they see, and commit writes that violate constraints when combined. True serializability prevents this; snapshot isolation does not. For many applications, write skew scenarios are rare enough that the performance gain justifies the risk, especially when supplemented with application-level checks or explicit locking on critical paths.

When Phantoms Still Matter

Despite snapshot isolation’s dominance, phantom reads remain relevant in specific scenarios. Distributed databases often provide weaker default isolation for performance across network partitions. MongoDB’s read committed allows phantoms. Cassandra’s eventual consistency makes them common.

Streaming databases and event stores reading from append-only logs naturally exhibit phantom-like behavior—new events appear as producers write them. Systems like Kafka or Pulsar require clients to understand that range queries may return different results as new messages arrive, even within a logical “transaction” context.

Analytical queries over live operational data frequently encounter phantoms when isolation is relaxed for read performance. Running a report that summarizes daily sales while transactions continue inserting orders means accepting that row counts may be approximate or that detailed and summary queries may not align perfectly.

Designing Around Isolation Anomalies

Understanding phantom reads helps engineers make informed isolation choices. For read-heavy workloads with rare conflicts, snapshot isolation provides excellent concurrency with intuitive consistency. For workflows requiring strict serializability—financial transfers, inventory allocation, seat reservations—explicit locks or serializable isolation become necessary despite the cost.

Modern databases increasingly offer granular isolation control per transaction or query. Postgres allows setting isolation level per transaction block. Distributed SQL systems like CockroachDB and YugabyteDB default to serializable but allow relaxing it for specific queries when architects understand the implications.

The key insight is that phantom reads, like other isolation anomalies, represent a fundamental tradeoff between consistency guarantees and concurrent throughput. Recognizing when your application truly requires protection against phantoms—and when snapshot isolation suffices—lets you extract performance where it matters without sacrificing correctness where it doesn’t.