Phantom Read Explained: When New Rows Appear Mid-Transaction

Scenario context

A phantom read occurs when a transaction runs the same range query twice and gets a different set of rows — because a concurrent transaction inserted or deleted matching rows and committed in between. Row locks cannot stop this because the phantom row did not exist when the first lock was acquired. Only SERIALIZABLE isolation — through gap locks in MySQL InnoDB or Serializable Snapshot Isolation (SSI) in PostgreSQ…

This walkthrough focuses on Why Row Locks Are Powerless Against Phantoms: The Predicate Lock Gap.

Before making a design choice, clarify:

• When most engineers think about preventing concurrent anomalies, they think about row locks: acquire a lock on the row before reading it, hold it for the transaction's duration, release at commit.
• This strategy works perfectly for non-repeatable reads — once T1 locks seat 1 , T2 cannot update it until T1 releases the lock.
• But a phantom read involves a row that did not exist when T1 ran its first query.

Concepts in play: database-isolation-levels-anomalies, caching, consistency-replication, jvm-internals-and-garbage-collection

Stage 1 — How a Phantom Read Unfolds: Step-by-Step Transaction Mechanics

• The sequence diagram below traces the SeatFast/QuickBook scenario through its exact interleaving.
• Read it left to right: SeatFast Transaction (T1) starts first, QuickBook Transaction (T2) executes and commits in the middle window, and T1 re-reads after T2 has committed.
• The key observation is in the gap between T1's first and second reads.
• T1 never touched seat 1 or seat 2 directly — it only ran a predicate query.

sequenceDiagram
    participant T1 as SeatFast Transaction
    participant DB as Availability DB
    participant T2 as QuickBook Transaction

    T1->>DB: SELECT seat_id WHERE section=A AND available=true
    DB-->>T1: Returns seat_1, seat_2, seat_3 (3 rows)
    Note over T1: T1 communicates 3 seats available. Booking flow begins.
    T2->>DB: UPDATE seats SET available=false WHERE seat_id IN (seat_1, seat_2)
    T2->>DB: INSERT INTO bookings (seat_id, user_id) VALUES (seat_1, u42)
    T2->>DB: INSERT INTO bookings (seat_id, user_id) VALUES (seat_2, u43)
    T2->>DB: COMMIT
    Note over T2,DB: T2 committed. seat_1 and seat_2 are now booked by u42 and u43.
    T1->>DB: SELECT seat_id WHERE section=A AND available=true
    DB-->>T1: Returns seat_3 only (1 row)
    Note over T1,DB: Row set changed under T1. Phantom read has occurred.
    T1->>DB: Proceeds to book seat_1 and seat_2 based on stale first query

Observable outcome: T1 never touched seat 1 or seat 2 directly — it only ran a predicate query.

Stage 2 — Deep Dive: How Databases Physically Block Phantom Reads

• Understanding phantom reads at the surface level is not enough to build reliable systems.
• Engineers need to understand how a database engine physically prevents a phantom — what data structures are locked, what dependency graphs are maintained, and what the performance cost is of each approach.
• The two dominant strategies — gap locking (MySQL InnoDB) and Serializable Snapshot Isolation (PostgreSQL) — both solve the phantom problem but through fundamentally different internal mechanisms.
• Internals: Gap Locking in B-Tree Indexes and rw-Dependency Tracking in SSI Every major relational database stores row data in a B-tree index ordered by primary key or a secondary index key.

Observable outcome: Internals: Gap Locking in B-Tree Indexes and rw-Dependency Tracking in SSI Every major relational database stores row data in a B-tree index ordered by primary key or a secondary index key.

Stage 3 — Isolation Level Anomaly Matrix: What Each Standard Level Actually Prevents

• The ANSI SQL standard defines four isolation levels, each specifying which concurrency anomalies it prevents.
• This table shows the full picture — including where phantom reads slip through.
• Two entries in this table often surprise engineers: 1.
• MySQL InnoDB's REPEATABLE READ prevents phantom reads — but only through gap locks, which can cause deadlocks and lock contention.

Observable outcome: MySQL InnoDB's REPEATABLE READ prevents phantom reads — but only through gap locks, which can cause deadlocks and lock contention.

Stage 4 — Where Phantoms Strike: The Reservation Service Architecture Under Concurrent Load

• The diagram below shows the standard layered architecture of a seat reservation platform.
• The phantom read vulnerability lives in the gap between the Reservation Service's first read and the Booking Engine's write — a window that exists in every stateless HTTP service that separates availability checking fro…
• This diagram illustrates why phantom reads are an architectural problem, not just a database curiosity.
• The Reservation Service reads from the Availability DB in one step, passes intent to the Booking Engine, which reads again before writing.

graph TD
    U[User Browser] --> RS[Reservation Service]
    RS --> ADB[(Availability DB - PostgreSQL)]
    ADB --> RS
    RS --> BE[Booking Engine]
    BE --> ADB
    BE --> CS[Confirmation Service]
    CS --> U
    CT[Concurrent Booking Transaction] --> ADB
    ADB --> CT

Observable outcome: The Reservation Service reads from the Availability DB in one step, passes intent to the Booking Engine, which reads again before writing.

Stage 5 — Gap Locks and Next-Key Locks in MySQL InnoDB: REPEATABLE READ's Hidden Defense

• MySQL InnoDB achieves phantom prevention at its default REPEATABLE READ level through a locking mechanism that goes beyond what the ANSI standard requires: gap locks and next-key locks .
• A gap lock locks the space between index records, preventing insertions into that gap.
• If T1 queries WHERE section = 'A' AND available = true , InnoDB locks not just the matching rows but also the index gaps adjacent to those rows.
• Any attempt by T2 to insert a new row whose index value would land in that gap is blocked until T1 commits or rolls back.

Observable outcome: Any attempt by T2 to insert a new row whose index value would land in that gap is blocked until T1 commits or rolls back.

Stage 6 — PostgreSQL's Serializable Snapshot Isolation: Conflict Detection Without Range Locking

• PostgreSQL takes a fundamentally different approach to phantom prevention.
• Rather than blocking concurrent writes with gap locks, PostgreSQL tracks read-write dependencies between transactions and detects potential serialization failures at commit time.
• This mechanism — Serializable Snapshot Isolation (SSI) — was introduced in PostgreSQL 9.1 and is based on Michael Cahill's 2008 research at the University of Sydney.
• SSI allows transactions to run with full snapshot isolation (every transaction sees a consistent snapshot of the database as of its start time) but maintains a dependency graph of which transactions read data that other…

Observable outcome: SSI allows transactions to run with full snapshot isolation (every transaction sees a consistent snapshot of the database as of its start time) but maintains a dependency graph of which transactions read data that other…

End-to-end outcome

After working through the stages, you should be able to explain:

• • Phantom read = a range query returns a different set of rows mid-transaction because a concurrent transaction inserted or deleted rows matching the query predicate and committed between the two reads.
• • The critical distinction from non-repeatable reads: phantom reads affect the population of rows returned by a predicate; non-repeatable reads affect the values of specific rows already read.

Alternatives considered

Phantom Read vs. Non-Repeatable Read: One Critical Distinction

These two anomalies are often conflated, but the distinction matters both conceptually and in terms of which isolation mechanism prevents each one.

When to Use SERIALIZABLE: A Decision Guide for Phantom Prevention

Not every workload requires SERIALIZABLE isolation.

Failure and degraded modes

Trade-offs and Failure Modes: When Phantom Prevention Becomes the Bottleneck

• Detection: Every mechanism for preventing phantom reads introduces a trade-off.
• User or operator impact: Every mechanism for preventing phantom reads introduces a trade-off.

Lessons Learned from Phantom Read Failures in Production

• Detection: "REPEATABLE READ" does not mean the same thing across databases — and the difference is phantom reads.
• User or operator impact: "REPEATABLE READ" does not mean the same thing across databases — and the difference is phantom reads.