Demystifying Phantom Reads: Anatomy of a Database Concurrency Bug

When multiple transactions execute concurrently in a relational database management system (RDBMS), data anomalies can emerge if the transaction isolation level is insufficient. One of the most subtle yet disruptive concurrency anomalies is the Phantom Read.

Phantom reads frequently manifest when operating under the Read Committed isolation level. This article explores the root cause of phantom reads, walks through an end-to-end practical reproduction using a social media profile counter, analyzes why data inconsistencies occur, and covers strategies to eliminate them.

---

1. What is a Phantom Read?

A Phantom Read occurs in a database transaction when a query executes a search condition (a range query or predicate read), and upon re-evaluating that exact query later within the same transaction, a different set of rows is returned because a concurrent transaction inserted, deleted, or updated rows satisfying that predicate and successfully committed.

Unlike a Non-Repeatable Read (where an existing row is modified or deleted by another transaction, altering the values of columns already fetched), a Phantom Read deals with the appearance or disappearance of new rows that match the query predicate.

Transaction 1                         Transaction 2
-------------                         -------------
BEGIN;
SELECT * WHERE user_id = 1;
(Returns rows: [1, 2, 3])
                                      BEGIN;
                                      INSERT INTO post (id, user_id) VALUES (4, 1);
                                      COMMIT;
SELECT * WHERE user_id = 1;
(Returns rows: [1, 2, 3, 4])  <-- Phantom Row!
COMMIT;

---

2. A Real-World Scenario: The Social Media Feed Counter

To see how this creates severe application bugs, consider a typical social media platform where performance requirements necessitate caching counters in a dedicated statistics table rather than calculating expensive COUNT(*) aggregates on every user profile view.

The Schema

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE post (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE user_stats (
    user_id INT PRIMARY KEY,
    total_post INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Initial state:

• users: User 1 exists.
• post: User 1 has 3 existing posts (id: 1, 2, 3).
• user_stats: User 1 has total_post = 3.

The Post Publication Workflow

When a user publishes a new post via an API endpoint, the application executes three operations inside a single atomic transaction:

1. Insert the new post into the post table.
2. Recompute and persist the counter in user_stats using a subquery:

   UPDATE user_stats 
   SET total_post = (SELECT COUNT(id) FROM post WHERE user_id = 1) 
   WHERE user_id = 1;

3. Query all posts made by the user to return them in the API response payload so the client app can immediately refresh the user’s feed:

   SELECT * FROM post WHERE user_id = 1;

---

3. Step-by-Step Reproduction Under READ COMMITTED

Set the session transaction isolation level to READ COMMITTED and disable autocommit:

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Now consider two concurrent API requests (Request A and Request B) triggered at almost the exact same instant by User 1 publishing two posts simultaneously across two database connections (T1 and T2).

Execution Timeline

           Transaction T1 (Session 1)                   Transaction T2 (Session 2)
                     |                                              |
   [T1] BEGIN;       |                                              |
                     |                             [T2] BEGIN;      |
   INSERT post (4,1);|                                              |
                     |                             INSERT post (5,1);|
   UPDATE user_stats |                                              |
   (count = 4);      |                                              |
                     |                             COMMIT;          |
                     |                                              |
   SELECT * FROM post|                                              |
   WHERE user_id = 1;|                                              |
   (Returns 5 rows!) |                                              |
                     |                                              |
   COMMIT;           |                                              |
                     v                                              v

Detailed Trace

1. T1 inserts its post:

   -- T1 executes:
   INSERT INTO post VALUES (4, 1);

2. T1 computes and updates the counter:

   -- T1 executes:
   UPDATE user_stats 
   SET total_post = (SELECT COUNT(id) FROM post WHERE user_id = 1) 
   WHERE user_id = 1;

   At this moment, T1 sees its own uncommitted insert (post 4) alongside existing posts (1, 2, 3). The count evaluated is 4.

3. T2 executes concurrently:

   -- T2 executes:
   INSERT INTO post VALUES (5, 1);
   COMMIT;

   T2 commits post 5 to the database.

4. T1 fetches all posts to formulate the API response:

   -- T1 executes:
   SELECT * FROM post WHERE user_id = 1;

   Because T1 is running under READ COMMITTED, each read statement generates a fresh snapshot of the database reflecting all data committed up to that exact instant.

   As a result, T1 reads:

   • Posts 1, 2, 3 (initial)
   • Post 4 (its own uncommitted modification)
   • Post 5 (committed by T2 while T1 was in-flight)

   T1 retrieves 5 rows.

5. T1 completes and commits:

   COMMIT;

The Consequence: Application Inconsistency

• The database table user_stats.total_post was updated by T1 to value 4.
• The API response generated by T1 returns 5 post records.
• In the user interface, the profile header shows: Total Posts: 4, while the list below displays 5 distinct posts.

Out of nowhere, a “phantom” row materialized mid-transaction, desynchronizing the aggregated counter from the returned dataset.

---

4. Why Does READ COMMITTED Allow Phantom Reads?

In the ANSI SQL-92 isolation specification, isolation levels are defined based on the anomalies they permit:

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Uncommitted	Permitted	Permitted	Permitted
Read Committed	Prevented	Permitted	Permitted
Repeatable Read	Prevented	Prevented	Engine Dependent / Prevented
Serializable	Prevented	Prevented	Prevented

Under READ COMMITTED:

• The storage engine uses Multi-Version Concurrency Control (MVCC) to provide consistent non-locking reads.
• However, a new read view (snapshot) is generated for every individual query execution within the transaction.
• Because each SELECT establishes a new point-in-time snapshot, any rows committed by other transactions between statement executions become immediately visible.

---

5. How to Mitigate Phantom Reads

Solution 1: Elevate to REPEATABLE READ

The most standard mitigation is setting the isolation level to REPEATABLE READ.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Mechanism in MySQL (InnoDB):

Under REPEATABLE READ, InnoDB establishes a single MVCC snapshot when the first read statement is executed inside the transaction. All subsequent plain SELECT queries reuse this identical read view throughout the entire transaction lifetime.

If T2 commits new rows while T1 is running, T1 continues reading from its original snapshot and will not observe the rows committed by T2, entirely preventing phantom reads on plain SELECT queries.

Solution 2: Explicit Locking Reads (Predicate / Next-Key Locking)

If an application must operate under READ COMMITTED or requires strictly serialized read-write guarantees, it can use locking reads:

-- Acquire shared or exclusive locks on the read range
SELECT * FROM post WHERE user_id = 1 FOR UPDATE;

How Locking Handles Phantoms:

• In storage engines like InnoDB, SELECT ... FOR UPDATE or SELECT ... FOR SHARE does not rely purely on MVCC snapshots. Instead, it places record locks on matched rows and gap locks on the index gaps between and surrounding those rows (known as Next-Key Locks).
• A gap lock explicitly prevents concurrent transactions from inserting any new record where user_id = 1 until the holding transaction commits or rolls back.
• In our scenario, when T1 holds a next-key lock on WHERE user_id = 1, T2’s INSERT INTO post VALUES (5, 1) blocks until T1 finishes, eliminating the race condition.

---

6. Engine-Specific Variations

Different relational database engines handle phantom reads differently even under the same ANSI standard labels:

• MySQL (InnoDB): InnoDB’s default isolation level is REPEATABLE READ. By combining consistent snapshots (MVCC) for non-locking reads and Next-Key Locks for locking reads/DML statements, InnoDB effectively prevents phantom reads at REPEATABLE READ without requiring full SERIALIZABLE isolation in most workloads.
• PostgreSQL: PostgreSQL’s default is READ COMMITTED. Its REPEATABLE READ implementation uses snapshot isolation (SI). It prevents both non-repeatable reads and phantom reads by enforcing that all queries in the transaction see the snapshot taken at transaction start. Concurrent conflicting writes result in serialization failures (ERROR: could not serialize access due to concurrent update), requiring application-level retries.

---

7. Key Takeaways

1. Definition: A Phantom Read occurs when a transaction queries a range of rows twice and discovers newly committed rows (or missing deleted rows) in the second read.
2. Root Cause in READ COMMITTED: In READ COMMITTED, every SQL statement receives an updated MVCC snapshot, exposing newly committed records mid-transaction.
3. Real-World Impact: Interleaving dependent writes, aggregations, and reads can lead to silent discrepancies between cached counters and underlying row collections.
4. Prevention:
   • Upgrade the transaction isolation level to REPEATABLE READ to maintain a persistent snapshot across the transaction.
   • Employ Next-Key Locking via SELECT ... FOR UPDATE to block concurrent insertions into the scanned key range.
   • Ensure database-level configurations match the consistency and concurrency demands of your specific business domain.