---

🟢 Junior Level

A Phantom Read is a database concurrency anomaly where a transaction executes a range or predicate search query (e.g., WHERE status = 'PENDING'), and upon re-executing the identical query later within the same transaction, observes a different set of rows—new rows appear or previous rows disappear—because another concurrent transaction inserted (INSERT) or deleted (DELETE) matching rows and successfully executed a COMMIT in the interim.

The newly surfaced rows that did not exist during the initial read are formally referred to as “phantom rows”.

Execution Timeline

Timeline  Transaction A (Daily Audit Report)             Transaction B (New Order Ingestion)
   |      ----------------------------------             -----------------------------------
   |  T1: BEGIN;
   |  T2: SELECT COUNT(*) FROM orders                    -- Result: 10 orders
   |      WHERE status = 'PENDING';
   |  T3:                                                BEGIN;
   |  T4:                                                INSERT INTO orders (id, status, amount)
   |                                                     VALUES (11, 'PENDING', 5000);
   |                                                     COMMIT; -- Formally committed!
   |  T5: SELECT SUM(amount) FROM orders                 -- Calculates total across 11 orders!
   |      WHERE status = 'PENDING';                      -- Desynchronized audit: count=10, sum of 11!
   v  T6: COMMIT;

Spring Transactional Context

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class OrderAuditService {

    private final OrderRepository orderRepository;

    public OrderAuditService(OrderRepository orderRepository) {
        this.orderRepository = orderRepository;
    }

    // Elevating to REPEATABLE_READ freezes the MVCC snapshot in PostgreSQL/MySQL,
    // ensuring both queries evaluate the exact same set of records:
    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public AuditReport calculateDailySummary() {
        long count = orderRepository.countByStatus("PENDING");
        BigDecimal total = orderRepository.sumByStatus("PENDING");
        return new AuditReport(count, total);
    }
}

Analogy: A tour guide counts the tourists boarded on a bus: “Exactly 20 passengers present.” While the guide turns around to record this number in the logbook, two latecomers quietly climb in through the rear door. The guide counts again before departure: now there are 22 passengers. Those two unexpected new passengers are phantoms.

---

🟡 Middle Level

Non-Repeatable Read vs. Phantom Read

This distinction is frequently tested in senior interviews:

Criterion	Non-Repeatable Read (Fuzzy Read)	Phantom Read
What Changes	Column values in an already existing row	The number and identity of rows in the result set
Triggering SQL	UPDATE (or point DELETE on a single ID)	INSERT (or range-based DELETE)
Query Predicate	Exact point lookup: WHERE id = 42	Predicate range or condition: WHERE price > 100
Engine Defense	Row-level locks (SELECT ... FOR UPDATE)	Index Gap Locks (MySQL) or MVCC Snapshot / Predicate Locks
System Impact	Inconsistent business calculation on an entity	Inconsistent reports, broken aggregations (COUNT, SUM)

ANSI SQL-92 Specification vs. Real-World Database Engines

According to the formal ANSI SQL-92 standard, the Repeatable Read isolation level must prevent Dirty Reads and Non-Repeatable Reads, but is permitted to allow Phantom Reads. However, modern relational database engines often exceed the baseline standard:

Database Engine	Phantoms on Repeatable Read?	Underlying Defense Mechanism
PostgreSQL	❌ Prevented (for plain SELECT)	MVCC Snapshot established at the first query
MySQL (InnoDB)	❌ Prevented (for SELECT & FOR UPDATE)	Next-Key Locks (Record Lock + Gap Lock)
Oracle	❌ Prevented	Statement-level / Transaction-level MVCC snapshot
MS SQL Server	⚠️ Permitted!	Acquires shared locks only on existing rows; gaps remain unprotected

---

🔴 Senior Level

Next-Key Locking Internals in MySQL (InnoDB)

In MySQL InnoDB under Repeatable Read, locking queries (such as SELECT ... FOR UPDATE or UPDATE) prevent phantom reads via Next-Key Locking:

\[\text{Next-Key Lock} = \text{Record Lock (Index Record)} + \text{Gap Lock (Preceding Interval)}\]

Consider an index containing entries with keys $5$, $10$, and $15$:

-- Query executed by Transaction T1:
SELECT * FROM orders WHERE id BETWEEN 8 AND 12 FOR UPDATE;

What InnoDB Locks:

1. Record Lock: Locks the existing index record id = 10.
2. Gap Lock (left): Locks the gap interval $(5, 10)$—no concurrent transaction can execute INSERT with $id \in [6, 9]$.
3. Gap Lock (right / Next-Key): Locks the gap interval $(10, 15)$ up to the next record—no concurrent transaction can execute INSERT with $id \in [11, 14]$.

Index Keys:    ... 5               10              15 ...
Gaps:              (--- Gap 1 ---)    (--- Gap 2 ---)
                   ^                  ^               ^
Locks Placed:      Gap Lock (5, 10)   Record Lock 10  Gap Lock (10, 15)

[!WARNING] Gap Lock Deadlock Hazard: Gap locks are non-exclusive on acquisition (multiple transactions can hold overlapping gap locks on the same interval). However, they are mutually exclusive with INSERT operations. If Transaction A and Transaction B both hold gap locks over $(5, 10)$ and subsequently both attempt an INSERT into that gap, neither can proceed, causing an immediate database deadlock.

Predicate Locks in PostgreSQL (SSI - Serializable Snapshot Isolation)

PostgreSQL on Repeatable Read relies on MVCC snapshots for plain SELECT statements (it does not use gap locks). For complete mathematical serializability (SERIALIZABLE), PostgreSQL employs Serializable Snapshot Isolation (SSI):

1. When a predicate query like SELECT * FROM orders WHERE status = 'PENDING' runs, PostgreSQL registers an in-memory Predicate Lock (SIREAD lock).
2. These locks are attached to the B-Tree index pages covering the range matching the predicate.
3. If a concurrent transaction inserts a new row that lands in that same index page, the PostgreSQL lock manager detects an incoming rw-antidependency ($T_{\text{reader}} \xrightarrow{rw} T_{\text{inserter}}$).
4. If a cycle is formed in the serialization graph ($T_1 \xrightarrow{rw} T_2 \xrightarrow{rw} T_1$), PostgreSQL immediately terminates one of the transactions with SQLSTATE 40001 (could not serialize access due to read/write dependencies among transactions).

The “Phantom Update” Anomaly in PostgreSQL Repeatable Read

While plain SELECT under PostgreSQL Repeatable Read is immune to phantom reads, data modification statements (UPDATE, DELETE) operate on the latest committed physical versions (Current Read):

-- Transaction T1 (Repeatable Read):
SELECT count(*) FROM orders WHERE status = 'PENDING'; -- Returns 5 rows

-- Concurrent Transaction T2:
INSERT INTO orders (status) VALUES ('PENDING'); -- Inserts 6th row!
COMMIT;

-- Transaction T1 executes a bulk update:
UPDATE orders SET status = 'PROCESSED' WHERE status = 'PENDING';
-- Result: "UPDATE 6" (T1 modified a row it could not see!)

-- Transaction T1 re-reads:
SELECT count(*) FROM orders WHERE status = 'PROCESSED'; -- Returns 6 rows!

Why this occurs: By MVCC visibility rules, an update writes a new tuple version owned by the current transaction. Every transaction is always permitted to observe tuples created or modified by itself, creating the illusion that a phantom row materialized out of thin air.

---

4 Tricky Questions

1. Why does the ANSI SQL standard permit Phantom Reads under Repeatable Read, whereas PostgreSQL prevents them for plain SELECT?

Answer: The ANSI SQL-92 specification was formulated during an era dominated by Two-Phase Locking (2PL) concurrency models. Under pure 2PL, achieving Repeatable Read required acquiring shared read locks (S-locks) exclusively on existing rows returned by the query. Because gaps between rows were left unshared and unlocked, concurrent transactions could freely insert new records into those gaps, inevitably causing phantoms.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) rather than pure 2PL. When a Repeatable Read transaction begins, its snapshot freezes the transaction horizon (xmin / xmax). Any row inserted by a concurrent transaction receives an xmin newer than the active snapshot horizon, and PostgreSQL’s query execution engine automatically filters it out during subsequent SELECT statements.

---

2. Can SELECT ... FOR UPDATE under Read Committed prevent phantom reads when inserting new records?

Answer: No. Under Read Committed, SELECT ... FOR UPDATE acquires exclusive row locks (X-locks) strictly on records that already physically exist in the table at the instant the statement executes. When a concurrent transaction executes an INSERT:

1. The new row does not yet exist in the table, so it cannot be locked by the preceding SELECT ... FOR UPDATE.
2. Under Read Committed, relational engines (both PostgreSQL and MySQL InnoDB) do not place Gap Locks for non-unique range lookups.
3. The concurrent INSERT completes and commits unimpeded.

A subsequent execution of SELECT ... FOR UPDATE within the first transaction will observe and lock the new row, manifesting a phantom read. Preventing phantoms requires either Repeatable Read in MySQL InnoDB (via Next-Key Locks) or Serializable isolation.

---

3. How can temporary tables compromise phantom read protection under PostgreSQL Serializable?

Answer: In PostgreSQL, the Serializable Snapshot Isolation (SSI) engine maintains predicate locks (SIREAD locks) exclusively in shared memory for permanent relation catalogs and tables. Temporary tables (CREATE TEMP TABLE) are private to the backend process session and are intentionally omitted from shared-memory predicate lock tracking to maximize performance. If application logic copies a dataset into a temporary table, calculates aggregations or validates business constraints on the temp table, and writes conclusions to permanent tables, SSI cannot track the rw-antidependencies on the temp table. This leads to undetected serialization anomalies and phantom records slipping past the validator.

---

4. What is the fundamental performance difference between MySQL Gap Locking and PostgreSQL SSI Predicate Locking?

Answer:

1. Blocking vs. Optimistic Aborts:
   • MySQL InnoDB Gap Locks are pessimistic and blocking: concurrent INSERT statements attempting to land in an active gap are forced to wait. This reduces transaction throughput and frequently induces deadlocks under high write concurrency.
   • PostgreSQL SSI Predicate Locks are optimistic and non-blocking: they never cause concurrent INSERT statements to wait. Both transactions proceed concurrently. PostgreSQL detects dependency cycles and aborts one transaction only at COMMIT time with error 40001.
2. Memory Footprint:
   • InnoDB stores gap locks inside its lock table as bitmap structures.
   • PostgreSQL SSI maintains predicate locks across tuples, pages, and relations in a dedicated shared memory area (max_pred_locks_per_transaction). If this memory fills up, PostgreSQL aggressively promotes page locks to table locks, increasing false-positive serialization abort rates.

---

🎯 Interview Cheat Sheet

• Core Definition: A repeated range/predicate query (WHERE condition) returns a different row count or set because a concurrent transaction committed an INSERT or DELETE.
• Phantom Read vs. Non-Repeatable Read:
  • Non-Repeatable Read: An UPDATE or DELETE modifies an existing row (WHERE id = ?).
  • Phantom Read: An INSERT or range DELETE alters the membership of a range (WHERE status = ?).
• Engine Implementations at Repeatable Read:
  • PostgreSQL: MVCC snapshot prevents phantoms for pure SELECT queries.
  • MySQL InnoDB: Next-Key Locks (Record Lock + Gap Lock) physically block insertions into range intervals.
  • MS SQL Server: Allows phantom reads; requires SERIALIZABLE or Snapshot Isolation.
• Phantom Update Anomaly: In PostgreSQL Repeatable Read, UPDATE operates as a Current Read. Bulk updates can modify and unmask phantom records inserted by concurrent transactions.
• Next-Key Lock Structure: $\text{Record Lock} + \text{Gap Lock}$. Prevents range inserts but is a primary cause of database deadlocks.

Related Topics

• What is Repeatable Read
• What is Serializable
• What is Non-Repeatable Read
• What is Lost Update
• What is the Default Isolation Level in MySQL