Database Concurrency Anomalies: Dirty Reads, Non-Repeatable Reads, Phantoms & Lost Updates

Two users, Alice and Bob, simultaneously click “Buy Now” on an e-commerce ticketing site for the last remaining front-row seat (Seat #42, $150).

Alice clicks “Purchase”. Her application thread opens a database transaction, deducts $150 from her account, marks Seat #42 as RESERVED, and prepares to send confirmation.

At the exact same millisecond, Bob loads the seating chart. His request reads the database rows.

If your database allows Bob to read Alice’s modified row before Alice completes payment, Bob sees Seat #42 marked as reserved. But 500 milliseconds later, Alice’s credit card declines. Her transaction executes a ROLLBACK.

Seat #42 reverts to AVAILABLE on disk. But Bob’s application layer already rendered a page telling him the seat was sold out. Even worse: if Bob’s thread attempted to calculate event revenue based on Alice’s uncommitted $150 write, Bob’s analytics system recorded phantom money that vanished into thin air.

This is a Dirty Read. It is just one of several concurrency anomalies that occur when multiple SQL connections execute operations against shared data pages simultaneously.

To design safe systems, you must understand how data races manifest inside storage engines when isolation guarantees break down.

The Root Cause of Concurrency Anomalies

Databases process queries using concurrent worker threads. If every SQL transaction executed serially—one after another—concurrency anomalies would never exist. But single-threaded database engines cannot scale across multi-core CPUs.

When multiple transactions read and write shared data pages concurrently without synchronization, four fundamental memory-level conflicts occur:

Write-Read Conflict (Dirty Read): Transaction A reads data modified by Transaction B before Transaction B commits.
Read-Write Conflict (Non-Repeatable Read): Transaction A reads a row, Transaction B updates or deletes that row and commits, and Transaction A reads the exact same row again—getting different column values.
Phantom Read Conflict: Transaction A executes a range query (e.g., WHERE age > 30). Transaction B inserts a new row matching the range criteria and commits. Transaction A re-runs the range query and discovers a “phantom” record.
Write-Write Conflict (Lost Update): Transaction A and Transaction B read the same row state concurrently. Both compute a update locally, and both execute a WRITE. The second write overwrites the first write without accounting for the first write’s changes.

1. Dirty Read (Write-Read Conflict)

A Dirty Read occurs when Transaction 1 reads uncommitted changes produced by Transaction 2.

Time   Transaction 1 (User Analytics)          Transaction 2 (Checkout Service)
----------------------------------------------------------------------------------
T1     BEGIN;                                  BEGIN;
T2                                             UPDATE accounts SET balance = balance - 150 
                                               WHERE user_id = 42;  -- Balance becomes 50
T3     SELECT balance FROM accounts 
       WHERE user_id = 42;
       -- Sees balance = 50 (DIRTY READ!)
T4                                             ROLLBACK; 
                                               -- Balance reverts to 200 on disk!
T5     SELECT balance * 0.10 AS tax 
       FROM accounts WHERE user_id = 42;
       -- Calculates tax based on 50 instead of 200!
T6     COMMIT;

Why It Happens

Transaction 2 modifies a page in the InnoDB Buffer Pool. The engine updates the row in-memory before writing an Undo Log record. If Transaction 1 reads the memory page directly without verifying transaction commit status, it consumes dirty data that never existed on disk.

2. Non-Repeatable Read (Fuzzy Read)

A Non-Repeatable Read occurs when a transaction reads the same row twice during its lifetime, but receives different field values because another transaction modified and committed that row in between.

Time   Transaction 1 (Invoice Generator)       Transaction 2 (Customer Profile Update)
----------------------------------------------------------------------------------------
T1     BEGIN;
T2     SELECT email FROM users WHERE id = 10;
       -- Returns '[email protected]'
T3                                             BEGIN;
T4                                             UPDATE users SET email = '[email protected]'
                                               WHERE id = 10;
T5                                             COMMIT;
T6     SELECT email FROM users WHERE id = 10;
       -- Returns '[email protected]' (NON-REPEATABLE READ!)
T7     COMMIT;

Why It Matters

If Transaction 1 is generating a PDF invoice requiring invariant state across 10 queries, reading changing row values midway produces corrupt financial documents where header fields contradict line items.

3. Phantom Read (Range Scan Anomaly)

While Non-Repeatable Read applies to modification of existing rows, a Phantom Read applies to the appearance or disappearance of rows matching a predicate query.

Time   Transaction 1 (Payroll Auditor)         Transaction 2 (HR Onboarding)
----------------------------------------------------------------------------------------
T1     BEGIN;
T2     SELECT COUNT(*) FROM employees 
       WHERE salary >= 100000;
       -- Returns 5 rows
T3                                             BEGIN;
T4                                             INSERT INTO employees (name, salary) 
                                               VALUES ('Charlie', 120000);
T5                                             COMMIT;
T6     SELECT SUM(salary) FROM employees 
       WHERE salary >= 100000;
       -- Aggregates 6 rows! The 6th row is a PHANTOM.
T7     COMMIT;

The Difference: Non-Repeatable Read vs Phantom

Non-Repeatable Read: Row count stays the same, but internal column attributes change on an existing row.
Phantom Read: New rows enter (or leave) the index range scanned by a query predicate.

4. Lost Update (Write-Write Conflict)

The Lost Update anomaly is one of the most dangerous application-level database bugs because it silently destroys data without raising an explicit SQL error.

Time   Transaction 1 (Service A)               Transaction 2 (Service B)
----------------------------------------------------------------------------------------
T1     BEGIN;                                  BEGIN;
T2     SELECT inventory FROM items             SELECT inventory FROM items 
       WHERE id = 99;                          WHERE id = 99;
       -- Reads inventory = 10                 -- Reads inventory = 10
T3     -- Computes 10 - 2 = 8                  -- Computes 10 - 5 = 5
T4     UPDATE items SET inventory = 8          
       WHERE id = 99;
T5     COMMIT;
T6                                             UPDATE items SET inventory = 5
                                               WHERE id = 99;
T7                                             COMMIT;

The Catastrophic Result

Service A deducted 2 items (inventory should be 8). Service B deducted 5 items (inventory should be 3).

Because Service B wrote inventory = 5 based on its stale snapshot of 10, Service A’s update was completely lost. 2 items vanished from accounting without trace.

ANSI SQL Isolation Levels Matrix

To protect applications against these anomalies, the standard SQL specification defines four transaction isolation levels. Each level guarantees protection against specific failure modes:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Lost Update
READ UNCOMMITTED	Allowed	Allowed	Allowed	Allowed
READ COMMITTED	Prevented	Allowed	Allowed	Allowed
REPEATABLE READ	Prevented	Prevented	Allowed (SQL standard)*	Prevented
SERIALIZABLE	Prevented	Prevented	Prevented	Prevented

MySQL InnoDB Note: Unlike standard SQL specifications, MySQL’s implementation of REPEATABLE READ uses Multi-Version Concurrency Control (MVCC) and Next-Key Gap Locking to prevent both Non-Repeatable Reads and Phantom Reads during normal SELECT queries!

How Engines Prevent Anomalies

Database engines use two major mechanics to enforce these isolation guarantees:

Pessimistic Concurrency Control (Locking): Transactions acquire locks on data pages or rows (Shared S locks for reads, Exclusive X locks for writes). Threads block until locks unlock. (Explored in Post 08).
Optimistic Concurrency Control & MVCC (Multi-Version Snapshot Reads): Instead of locking rows on read, the storage engine reads historical row versions from Undo Logs based on a point-in-time snapshot. (Explored in Post 09).

Summary & Next Steps

Concurrently executing SQL queries without isolation mechanisms leads to corrupted accounts, missing inventory, and phantom data.

Dirty Reads corrupt state by reading uncommitted buffer changes.
Non-Repeatable Reads break single-row value invariance across reads.
Phantom Reads alter row counts across predicate range scans.
Lost Updates overwrite concurrent writes silently.

In the next article, we dive deep into Two-Phase Locking (2PL), Shared vs Exclusive Locks, and Deadlock Resolution Algorithms.

References & Further Reading

Johnson, T., & Shasha, D. (1994). 2Q: A Low Overhead High Performance Buffer Management Replacement Algorithm. Proceedings of VLDB, 439–450.
Megiddo, N., & Modha, D. S. (2003). ARC: A Self-Tuning, Low Overhead Replacement Cache. Proceedings of FAST ‘03, 115–130.
Oracle Corporation. MySQL 8.0 Reference Manual: Buffer Pool LRU Algorithm. MySQL Docs.