TLDR: 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 PostgreSQL — guarantees range-query stability. Every airline, hotel, and e-commerce flash sale that has ever oversold inventory did so because phantom reads at weaker isolation levels went unguarded.

---

It is 09:14:02 UTC on a Saturday morning. The SeatFast reservation service handles the launch of a new concert. A user in London opens the seat map for Section A. The service queries the availability database:

SELECT seat_id FROM seats WHERE section = 'A' AND available = true

The database returns three rows: seat_1, seat_2, seat_3. SeatFast renders "3 seats available" and begins the booking flow. The user selects two seats and clicks Confirm.

Meanwhile — at 09:14:03 UTC — the QuickBook mobile app processes a concurrent request from a user in Manchester. QuickBook books seat_1 and seat_2, updates both rows to available = false, and commits. Total elapsed time for QuickBook's transaction: 800 milliseconds.

At 09:14:04 UTC, SeatFast's transaction re-queries the same range to validate availability before finalising the booking:

SELECT seat_id FROM seats WHERE section = 'A' AND available = true

The database now returns only seat_3. The set of matching rows has changed — not because any single row SeatFast previously read was updated (that would be a non-repeatable read), but because the population of rows matching the predicate has shifted beneath it. SeatFast has already told the London user that three seats were available. It is now mid-booking with stale data.

This is a phantom read — the row-range equivalent of a non-repeatable read, and one of the most misunderstood concurrency anomalies in relational databases.

---

📖 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.

A non-repeatable read is a single-row problem: Transaction A reads row R, Transaction B updates and commits R, and Transaction A reads R again in the same transaction and gets a different value. The row existed both times — its content changed.

A phantom read is a predicate problem: Transaction A runs a range query with predicate P and gets result set S. Transaction B inserts or deletes rows that satisfy P and commits. Transaction A re-runs the same range query and gets a different result set S'. No individual row that A previously read has changed — but new rows have appeared (or existing ones have vanished) because they match the predicate.

Think of it like a headcount at a conference. You count 50 attendees. You step away. Someone checks in 10 more people. You count again and find 60. No single attendee "changed" — the population of the room changed. The headcount is the phantom read; a name-badge colour change on an existing attendee would be the non-repeatable read.

The practical implication is significant: REPEATABLE READ isolation protects against non-repeatable reads but does not always protect against phantom reads. A database running at REPEATABLE READ will guarantee that any row you have already touched will not change value — but it makes no promise about rows you haven't touched yet that might match your query predicate.

---

⚙️ 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.

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

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. T2 changed the rows that satisfied that predicate, and since T1 holds no locks on rows it merely read (under READ COMMITTED or standard REPEATABLE READ), T2's commit is invisible to T1's locks but fully visible to T1's next query execution.

T1's business logic — "there are 3 available seats, proceed with booking" — is now operating on an assumption that ceased to be true at 09:14:03 UTC. The phantom read is not just a data inconsistency; it is a correctness failure in the application's decision-making process.

---

🧠 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. A standard row lock occupies a slot on a specific B-tree leaf node — it prevents other transactions from modifying that exact record, but says nothing about the space between records.

InnoDB gap locking works by occupying the space between two adjacent B-tree records. When a transaction reads with WHERE section = 'A' AND available = true, InnoDB scans the relevant index range and acquires both row locks on matched records and gap locks on the intervals between them. Any INSERT whose index key value would land in a locked gap is blocked. This is a pessimistic, blocking strategy: the inserting transaction waits until the gap lock holder commits or rolls back. The gap lock lives at the InnoDB buffer pool level — it does not touch disk, but it does consume lock table memory proportional to the number of open gaps.

PostgreSQL SSI takes a different internal path. Instead of locking index ranges, SSI maintains a predicate lock table (pg_predicate_locks) alongside the standard MVCC snapshot layer. Each SERIALIZABLE transaction that executes a range read acquires a SIREAD lock encoding the predicate. The engine then builds a read-write conflict graph: if transaction T2 writes data covered by T1's SIREAD lock, an rw-edge is added from T1 to T2. When the conflict graph contains a dangerous cycle (an rw-anti-dependency cycle where T1 → T2 and T2 → T1), the engine knows the two transactions cannot be serialised in any order that matches their reads — and rolls one back. Crucially, no blocking occurs during execution: both transactions run freely until commit, when the cycle is detected.

Performance Analysis: Throughput and Retry Overhead Across Isolation Levels

The choice of phantom prevention mechanism has measurable throughput implications. The table below compares the three main strategies under concurrent write-heavy workloads.

The key trade-off: gap locks reduce throughput during execution through blocking; SSI reduces throughput at commit time through rollbacks. For workloads with mostly non-conflicting transactions, SSI has lower overhead because the conflict detection rarely fires. For workloads with predictable access patterns and few genuine conflicts, gap locks may produce fewer retries than SSI's optimistic approach, since blocking prevents work from being discarded.

---

🔒 Why Row Locks Are Powerless Against Phantoms: The Predicate Lock Gap

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. At 09:14:02 UTC, seat_4 might not exist in the database at all. No row lock can be placed on seat_4 because there is nothing to lock. If T2 inserts seat_4 with available = true before T1 re-queries, T1 will see it as a new phantom row. Row-level locking is simply the wrong granularity for this class of anomaly.

What is required to block phantom reads is a predicate lock — a lock on the query condition itself, not on any specific row. A predicate lock on section = 'A' AND available = true says: "no transaction may insert, delete, or update any row in a way that would change the result of this predicate." Any attempt by T2 to insert a new qualifying row, or to update an existing row so that it enters or leaves the qualifying set, would be blocked until T1's predicate lock is released.

True predicate locks are expensive to implement because every new write must be checked against every outstanding predicate. In practice, databases approximate predicate locking with gap locks and next-key locks — range-based locks on index gaps that block insertions at a finer granularity than full-table locks but coarser than exact-predicate locks. This approximation is the foundation of InnoDB's phantom prevention at REPEATABLE READ.

---

📊 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. This is a MySQL-specific extension beyond the ANSI standard.
2. PostgreSQL's REPEATABLE READ still allows phantom reads — PostgreSQL uses MVCC snapshots, so you get a stable view of the data, but a range query executed a second time inside the same transaction can see new rows if the snapshot was taken after a concurrent insert committed. The safe choice in PostgreSQL for phantom prevention is SERIALIZABLE.

The column for write skew is included here because it represents the next anomaly that SERIALIZABLE closes. Many developers escalate to SERIALIZABLE for phantom prevention and get write-skew protection as a bonus.

---

🏗️ 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 from booking confirmation across two database round trips.

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

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. Each arrow to and from the database is a separate statement — and between any two arrows, a Concurrent Booking Transaction can commit. The phantom injection point sits on every edge between the Reservation Service and the database: any read-to-write gap is a window.

The fix is not to add more application-level checks — that adds more round trips and more windows. The fix is to close the window at the database layer, either through isolation level escalation or explicit range locking.

---

🔑 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.

A next-key lock combines a row lock on the current record with a gap lock on the gap immediately before it. This means InnoDB locks both the record itself and the space a new record would occupy before it — closing both the "update existing row" and "insert new row" phantoms simultaneously.

The trade-off is significant: gap locks can cause deadlocks that would not occur without them. If T1 holds a gap lock on the range [seat_10, seat_20) and T2 holds a gap lock on the range [seat_5, seat_15), the ranges overlap. If T1 tries to insert into T2's range and T2 tries to insert into T1's range simultaneously, they deadlock. InnoDB detects this and rolls back one transaction — which surprises engineers who expect REPEATABLE READ to be a "safe default."

Gap locks also affect single-row inserts in unexpected ways: an INSERT INTO seats (seat_id, section, available) VALUES (seat_7, 'A', true) can be blocked by a gap lock placed by a completely unrelated read transaction that happened to scan through the index range containing seat_7.

Key operational reality: if your workload runs high-concurrency inserts into tables with range queries, MySQL's gap locks at REPEATABLE READ will produce more lock contention than READ COMMITTED — which drops gap locks entirely and accepts phantom reads as a trade-off for higher throughput.

---

🐘 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 transactions subsequently wrote.

When PostgreSQL detects a rw-anti-dependency cycle — a pattern where T1 read data that T2 later wrote, and T2 also read data that T1 later wrote — it knows that both transactions cannot have executed in any serial order that would produce the same result. At that point, one transaction is rolled back with a serialization_failure error and the application is expected to retry.

The practical differences between SSI and gap locks:

PostgreSQL's REPEATABLE READ does not prevent phantom reads — it gives T1 a consistent MVCC snapshot so that it always sees the same version of rows it has read, but a re-executed range query can still return new rows that were inserted after T1's snapshot was taken. Only escalating to SERIALIZABLE activates SSI and closes the phantom gap.

---

🗄️ How Major Databases Handle Phantom Reads Differently

One of the most common errors in cross-database system design is assuming that REPEATABLE READ means the same thing everywhere. It does not.

A common misconception: Oracle and PostgreSQL both use MVCC and look similar, but PostgreSQL's SERIALIZABLE is SSI (optimistic, low overhead) while Oracle's SERIALIZABLE uses traditional full locking (pessimistic, high contention under write-heavy workloads). If you are migrating a schema that relies on Oracle's SERIALIZABLE behaviour, do not assume PostgreSQL's SERIALIZABLE has the same performance profile — SSI is generally lighter, but the retry pattern is different.

---

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

Every mechanism for preventing phantom reads introduces a trade-off. Choosing the wrong isolation level for a workload can introduce new failure modes that are just as damaging as the phantoms you were trying to prevent.

READ COMMITTED (phantom reads allowed): The highest-throughput option. No gap locks, no serialization tracking, no retries. Phantom reads are the accepted risk. The correct mitigation is application-level idempotency — unique constraints and idempotency keys catch the corruption at the write layer rather than preventing the inconsistent read. Suitable for analytics, reporting, and any workload where a range query result is informational rather than used to gate a write.

MySQL REPEATABLE READ (gap locks): Phantom reads are prevented, but gap lock deadlocks become the new failure mode. Two concurrent transactions that hold gap locks over overlapping ranges and then both attempt inserts inside each other's locked gap will deadlock. InnoDB auto-detects and rolls back one, but the rolled-back transaction's entire work is discarded. Applications must implement full transaction retry logic. Under extreme write concurrency on the same index range — such as a flash sale inserting thousands of new orders per second — gap lock contention can reduce effective insert throughput by 30–50% compared to READ COMMITTED.

PostgreSQL SERIALIZABLE (SSI): Phantom reads are prevented via optimistic conflict detection. Throughput degradation comes from two sources: (1) maintaining the SIREAD lock table in memory (controlled by max_pred_locks_per_transaction), and (2) transaction rollbacks on serialization_failure. The rollback rate scales with the actual conflict rate — low-conflict workloads see minimal overhead; high-conflict workloads see frequent retries. Unlike gap lock deadlocks, serialization_failure retries are clean: the transaction is fully rolled back with no partial state, and can be retried immediately with the same logic.

SELECT ... FOR UPDATE (selective range locking): The most surgical option. Only the specific range query acquires a lock; other reads in the same transaction remain non-blocking. The failure mode mirrors gap locks — overlapping FOR UPDATE ranges can deadlock — but the blast radius is smaller because only critical queries participate in locking.

---

🌍 Real-World Impact: Airlines, Flash Sales, and Financial Reconciliation

Phantom reads are not academic curiosities. Every overbooking story in the industry has a phantom read at its root.

Airlines and seat reservations: The canonical scenario. The seat inventory query is range-based — "show me all available seats in section X." Under READ COMMITTED, two passengers can simultaneously pass the availability check and both receive confirmation for the same seat. The airline must compensate one with a voucher or an upgrade. The cost of one overbooking event can exceed $1,000 per displaced passenger in regulatory penalties and customer recovery.

E-commerce flash sales: A flash sale for 100 units of a limited-edition product runs SELECT COUNT(*) FROM inventory WHERE product_id = 42 AND available = true. Under READ COMMITTED, 150 customers can pass the "stock available" check simultaneously before any single purchase commits. The merchant oversells by 50% in the first second of the sale. Fulfilment cancellations, refunds, and reputational damage follow.

Event ticketing and seat maps: Ticketing platforms serve seat-map renders by querying WHERE venue_id = X AND event_id = Y AND status = 'available'. Two users selecting the same seat from two browser tabs will both see "available" until one commits. Without serializable isolation or an explicit SELECT ... FOR UPDATE range lock, both clicks can proceed through checkout — and both tickets can be printed with the same seat number.

Financial double-credit: A bank's reconciliation job runs SELECT SUM(amount) FROM transactions WHERE account_id = X AND processed = false at the start of its batch. A concurrent payment processor inserts and commits a new qualifying transaction mid-batch. The reconciliation job's sum is now wrong, and the account receives incorrect credit.

In each case, the phantom read is invisible in test environments because QA never runs two users simultaneously with production-level concurrency. It surfaces first in production under load.

---

🔍 Detecting Phantom Reads Before They Cause Data Corruption

Phantom reads are often discovered through their downstream symptoms rather than direct observation. By the time a phantom read is identified as the root cause, overbooking has already occurred. A proactive detection strategy tracks the symptoms upstream.

Idempotency key violations: Booking systems that assign a unique key per (user, event, seat) will trigger a unique constraint violation when a duplicate booking attempts to insert. A sudden spike in unique constraint errors in the bookings table is a strong phantom read signal. Track this with a counter metric and alert on anomalous rates.

Inventory count divergence: Maintain a separate counter table (inventory_count) updated atomically alongside the products table. If inventory_count.available diverges from SELECT COUNT(*) FROM inventory WHERE available = true, phantom reads under concurrent writes are a likely cause. A scheduled reconciliation job that detects divergence and logs it provides early warning.

Booking confirmation conflicts: In distributed seat reservation systems, the confirmation step writes to a secondary audit log. A query over the audit log for duplicate (seat_id, event_id) entries with different user_id values directly surfaces overbooking events. Run this as a nightly integrity check and alert on any result count > 0.

Database wait events: On PostgreSQL, pg_stat_activity and pg_locks expose transaction wait chains. A pattern where many short SELECT transactions are waiting on lock acquisition from a SERIALIZABLE transaction indicates phantom prevention is active and contention is building. On MySQL, INFORMATION_SCHEMA.INNODB_TRX and INNODB_LOCK_WAITS show gap lock contention in real time.

Application retry spikes: If your application uses PostgreSQL SERIALIZABLE with a retry loop on serialization_failure (error code 40001), a spike in retry counter metrics indicates that SSI is detecting phantom read conflicts. The retries are working correctly — but high retry rates also mean throughput is degrading and the workload may need architectural intervention.

---

🧭 When to Use SERIALIZABLE: A Decision Guide for Phantom Prevention

Not every workload requires SERIALIZABLE isolation. The overhead — in the form of retries, lock contention, or tracking memory — must be justified by the actual risk. Use this guide to make the decision for your specific workload.

The most important insight in this table: SELECT ... FOR UPDATE is often the right scalpel when you need phantom prevention on a specific query without paying the full cost of SERIALIZABLE isolation on all reads in the transaction. It acquires the row-level and gap locks needed to protect the range, while leaving other reads in the transaction unaffected.

---

🧪 Phantom Reads in Practice: Three Scenarios That Show the Window Opening and Closing

This section walks through three concrete scenarios that show phantom reads manifesting — and the prevention strategy that closes each window. Recognising these patterns in your own codebase is the practical skill this post is designed to build.

Scenario 1 — Seat Booking Without Protection (phantom read occurs at READ COMMITTED)

The reservation service issues a range query, communicates the result to the user, then writes a booking as two separate database round trips:

• Step 1: T1 queries WHERE section = 'A' AND available = true → 3 rows returned
• Step 2: T2 books seat_1 and seat_2, sets available = false, commits
• Step 3: T1 writes booking records for seat_1 and seat_2 based on the stale Step 1 result
• Step 4: UNIQUE constraint fires on (seat_1, event_id) — duplicate booking conflict
• Step 5: T1 rolls back; user sees an error after already receiving a confirmation screen

The check (Step 1) and the act (Step 3) are separated by time. T2 invalidated the check in the gap. The UNIQUE constraint in Step 4 catches the corruption — but only after a user-facing confirmation has already been rendered. The damage is customer experience, not data integrity.

Scenario 2 — Seat Booking With SELECT ... FOR UPDATE (phantom read closed)

• Step 1: T1 opens a transaction and issues: SELECT seat_id FROM seats WHERE section = 'A' AND available = true FOR UPDATE
• Step 2: T2 attempts UPDATE seats SET available = false WHERE seat_id = 'seat_1' → T2 blocks: seat_1 is held by T1's FOR UPDATE lock
• Step 3: T1 writes its booking records and commits; seats are marked unavailable
• Step 4: T2 resumes and finds seat_1 already available = false → makes a correct decision and either aborts or books seat_3

The FOR UPDATE acquired locks on the matched rows and the index gaps around them. T2's competing write was blocked until T1 committed. No phantom occurred — and no serialization failure retry was needed.

Scenario 3 — Flash Sale With PostgreSQL SERIALIZABLE (conflict detected by SSI)

• Step 1: T1 opens a SERIALIZABLE transaction; issues SELECT COUNT(*) FROM inventory WHERE product_id = 42 AND available = true → 100 units. A SIREAD lock is placed on the predicate range.
• Step 2: T2 (also SERIALIZABLE) reads the same range and begins decrementing the available count by 1.
• Step 3: PostgreSQL detects an rw-anti-dependency cycle: T1 read data that T2 wrote; T2 read data that T1 will write.
• Step 4: PostgreSQL rolls back T2 with serialization_failure (SQLSTATE 40001) at commit time.
• Step 5: T2's application catches the 40001 error, retries the entire transaction from Step 1 → reads the updated count of 99 and proceeds correctly.

The SSI engine detected that T1 and T2 operated on overlapping data in a way that could not be serialized, and rolled back T2 before any inconsistency was committed. No user-facing error occurred — the retry is invisible to the end user if handled correctly in the application layer.

---

🛠️ MySQL and PostgreSQL: Configuring Phantom-Safe Isolation in Practice

The following configuration and DDL-level settings directly control how each database handles phantom reads. These are the operational levers available to a DBA or infrastructure engineer without changing application code.

MySQL InnoDB — global isolation and lock timeout:

# my.cnf — server-level defaults
transaction_isolation          = REPEATABLE-READ   # default; gap locks prevent phantoms
innodb_lock_wait_timeout       = 50                # seconds before a blocked txn errors out
innodb_deadlock_detect         = ON                # auto-rollback on deadlock (default ON)

Gap locks are active at REPEATABLE-READ. To reduce gap lock contention at the cost of allowing phantom reads:

transaction_isolation = READ-COMMITTED   # disables gap locks; phantoms possible

PostgreSQL — session and transaction-level controls:

-- Per-session default: all transactions in this session run serializable
SET default_transaction_isolation = 'serializable';

-- Per-transaction: escalate a single critical transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- range query + booking write here
COMMIT;

PostgreSQL SSI tuning parameter (controls memory for tracking read-write dependencies):

# postgresql.conf
max_pred_locks_per_transaction = 64   # default 64; increase if serialization_failure is high

Explicit range lock — works at any isolation level:

-- Locks all rows matching the predicate for the transaction's duration.
-- Blocks concurrent inserts into the same index range (gap lock behaviour).
-- Use when you need phantom prevention on a specific query without full SERIALIZABLE.
SELECT seat_id
FROM seats
WHERE section = 'A' AND available = true
FOR UPDATE;

SQL Server — enabling snapshot isolation for phantom prevention:

-- Database-level: enable snapshot isolation (ALLOW_SNAPSHOT_ISOLATION)
ALTER DATABASE ReservationDB SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ReservationDB SET READ_COMMITTED_SNAPSHOT ON;

-- Session-level: use serializable range locking
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

For a full deep-dive on PostgreSQL's SSI implementation and tuning, see the PostgreSQL documentation on Serializable Snapshot Isolation and the companion post [./isolation-levels-in-databases].

---

📚 Lessons Learned from Phantom Read Failures in Production

1. "REPEATABLE READ" does not mean the same thing across databases — and the difference is phantom reads. MySQL InnoDB prevents phantoms at REPEATABLE READ via gap locks. PostgreSQL does not — its REPEATABLE READ is MVCC snapshot isolation, and range queries can still return new rows. Teams migrating from MySQL to PostgreSQL without understanding this have reintroduced phantom read vulnerabilities they thought were solved.

2. Row locks protect what existed; they cannot protect what doesn't exist yet. Engineers often assume that locking a set of rows after a SELECT protects the result set. It does not protect against new rows being inserted that would match the same predicate. The correct mental model is: row locks protect values; predicate/gap locks protect ranges.

3. The check-then-act pattern across two HTTP requests is never atomically safe. A common microservices pattern is: Service A checks availability via HTTP → user confirms → Service A books. Between the check and the confirm, any number of concurrent transactions can invalidate the check. Application-level checks cannot substitute for database-level serialization. Use SELECT ... FOR UPDATE or SERIALIZABLE to make the check and the act atomic within a single database transaction.

4. Gap lock deadlocks in MySQL are a side effect of phantom prevention, not a bug. Teams running MySQL at REPEATABLE READ with concurrent inserts into indexed tables will encounter deadlocks that seem unrelated to any obvious contention. These are gap lock interactions — T1's range read placed a gap lock that overlaps with T2's insert gap lock. The fix is either accepting the retry cost, switching to READ COMMITTED with explicit locks on critical queries, or rethinking insert patterns to reduce range overlaps.

5. Idempotency keys and unique constraints are your phantom read safety net in production. Even with correct isolation levels, network retries and distributed failures can produce duplicate writes. A UNIQUE constraint on (seat_id, event_id) in the bookings table is the final line of defence that converts a phantom read data corruption into a caught constraint violation — which is infinitely more recoverable. Always pair isolation-level reasoning with constraint-level guarantees.

6. Monitoring serialization failures is as important as monitoring deadlocks. PostgreSQL SERIALIZABLE transactions retry on serialization_failure (SQLSTATE 40001). If your application does not log and meter these retries, you are flying blind. A sudden increase in retry rate means phantom read conflicts are increasing under load — which is a signal that either the workload is growing into a contention bottleneck or a query pattern has changed.

---

📌 Summary & Key Takeaways

• 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.
• Row locks cannot prevent phantom reads — the phantom row did not exist at the time the first lock was acquired. Predicate locks, gap locks, or next-key locks are required.
• REPEATABLE READ is not universally phantom-safe: MySQL InnoDB prevents phantoms at this level via gap locks; PostgreSQL does not — only SERIALIZABLE closes the gap in PostgreSQL.
• SELECT ... FOR UPDATE on a range is the surgical fix when you need phantom prevention on a specific critical query without paying the full overhead of transaction-wide serialization.
• Real-world phantom reads cause overbooking, inventory overselling, and duplicate ticket issuance. These failures consistently surface under load and are invisible in single-user test environments.
• The check-then-act pattern across separate requests is fundamentally unsafe without a database-level serialization guarantee in a single transaction.
• One-liner to remember: If your transaction's correctness depends on a count or a set of rows not changing between two reads, you need either SERIALIZABLE isolation or an explicit range lock — anything less is a phantom read waiting to happen.

---

📝 Practice Quiz

1. What is the defining characteristic that distinguishes a phantom read from a non-repeatable read?

   • A) A phantom read occurs on uncommitted data; a non-repeatable read occurs on committed data
   • B) A phantom read involves a change to the row set returned by a predicate; a non-repeatable read involves a value change in a specific row
   • C) A phantom read affects single-row queries; a non-repeatable read affects range queries
   • D) A phantom read is prevented by READ COMMITTED; a non-repeatable read is not

   Correct Answer: B

2. Transaction T1 is running at READ COMMITTED isolation in PostgreSQL. It executes SELECT COUNT(*) FROM orders WHERE status = 'pending' and receives 8. A concurrent transaction T2 inserts three new pending orders and commits. T1 re-executes the same query. What does T1 see?

   • A) 8 — PostgreSQL's READ COMMITTED caches the first result
   • B) 11 — T1 sees the newly committed rows on its second query
   • C) An error — concurrent inserts require T1 to retry
   • D) 8 — READ COMMITTED prevents phantom reads in PostgreSQL

   Correct Answer: B

3. A team migrates their seat reservation system from MySQL InnoDB (at default isolation) to PostgreSQL (at default isolation). They do not change any application or configuration settings. Which statement best describes the phantom read risk after migration?

   • A) Risk decreases — PostgreSQL's MVCC is more advanced than InnoDB's gap locking
   • B) Risk is unchanged — both databases default to REPEATABLE READ
   • C) Risk increases — MySQL prevents phantoms at REPEATABLE READ via gap locks, but PostgreSQL's REPEATABLE READ does not prevent phantoms
   • D) Risk decreases — PostgreSQL defaults to SERIALIZABLE isolation

   Correct Answer: C

4. Why can gap locks in MySQL InnoDB cause deadlocks?

   • A) Gap locks prevent commits from completing on time
   • B) Gap locks on overlapping index ranges held by different transactions can create circular wait conditions when both attempt to insert into each other's locked range
   • C) Gap locks are incompatible with foreign key constraints and cause lock escalation
   • D) Gap locks acquire table-level locks when the row count exceeds a threshold

   Correct Answer: B

5. Which isolation mechanism does PostgreSQL use to prevent phantom reads at SERIALIZABLE level?

   • A) Row-level locks held for the full transaction duration
   • B) Gap locks and next-key locks on index ranges
   • C) Serializable Snapshot Isolation — tracking read-write dependency cycles and rolling back transactions that would violate serializability
   • D) Two-phase locking with strict lock escalation to table level

   Correct Answer: C

6. A developer adds SELECT seat_id FROM seats WHERE section = 'A' AND available = true FOR UPDATE to their booking transaction. At which isolation level(s) does this prevent phantom reads?

   • A) Only at SERIALIZABLE — FOR UPDATE has no effect at lower levels
   • B) Only at REPEATABLE READ and above
   • C) At any isolation level — FOR UPDATE acquires a range lock that blocks concurrent inserts into the predicate range
   • D) Only when combined with BEGIN IMMEDIATE in SQLite

   Correct Answer: C

7. Which business failure pattern is most directly caused by an unguarded phantom read in an e-commerce inventory system?

   • A) A customer's credit card is charged twice for the same order
   • B) A product's price updates do not appear until the user refreshes the page
   • C) More units are sold than the recorded available stock because multiple transactions passed the availability check before any of them committed
   • D) A refund is processed but the inventory count is not incremented

   Correct Answer: C

8. A PostgreSQL application runs at SERIALIZABLE isolation and encounters frequent serialization_failure errors (SQLSTATE 40001). What is the correct handling strategy?

   • A) Downgrade to REPEATABLE READ — SERIALIZABLE is too strict for production
   • B) Add a FOR UPDATE clause to all queries to prevent serialization failures
   • C) Catch the error and retry the entire transaction — this is the expected behaviour of SSI under conflict
   • D) Increase max_pred_locks_per_transaction to eliminate all serialization failures

   Correct Answer: C

9. In the anomaly matrix, which isolation level prevents dirty reads, non-repeatable reads, and phantom reads, but still allows write skew in most database implementations?

   • A) READ COMMITTED
   • B) REPEATABLE READ
   • C) READ UNCOMMITTED
   • D) There is no standard level that prevents exactly those three and not write skew

   Correct Answer: D

10. (Open-ended challenge — no single correct answer) You are designing the booking confirmation flow for a high-traffic concert ticketing platform expecting 50,000 concurrent users during on-sale events. The current implementation reads seat availability in one service call and writes the booking in a second service call. What database-level and architectural changes would you recommend to eliminate phantom read vulnerabilities, and how would you evaluate the trade-offs between using SERIALIZABLE isolation versus explicit SELECT ... FOR UPDATE range locks versus application-level idempotency keys? Consider throughput, failure recovery, and operational complexity in your answer.

---

🔗 Related Posts

• Isolation Levels in Databases: Read Committed, Repeatable Read, Snapshot, and Serializable Explained
• Key Terms in Distributed Systems
• Data Anomalies in Distributed Systems
• Database Anomalies: SQL vs NoSQL
• BASE vs ACID: Choosing the Right Consistency Model
• Types of Locks Explained
• System Design: Distributed Transactions
• Consistency Patterns in Distributed Systems