Dirty Read, Phantom Read, Non-Repeatable Read Compared

1. Why concurrency anomalies happen

Concurrency anomalies like dirty read, non-repeatable read, and phantom read arise whenever two or more transactions overlap in time and at least one of them writes data the other reads. Without any isolation, each of these anomalies would be constantly possible, with full isolation none of them would be possible, but throughput would be severely limited. The SQL standard uses exactly these three anomalies to formally define the four isolation levels.

The crucial difference from abstract definitions is that each of these anomalies can be traced precisely through a concrete timeline of two transactions, here called T1 and T2. Anyone who has once seen how a dirty read or a phantom read arises step by step recognizes the risk profile of their own isolation level far faster than from a purely theoretical description.

The following sections show each anomaly with its own timeline, complemented by the less commonly named lost-update anomaly, and finally summarize which isolation level excludes which combination of these problems.

2. Dirty read: the riskiest anomaly

A dirty read occurs when a transaction reads data that another transaction has already written but not yet committed. If the writing transaction is later rolled back, the reading transaction worked with values that never actually existed in the database. This anomaly is the most dangerous of the three because decisions are made based on data that was formally never valid.

-- Dirty read timeline (only possible under READ UNCOMMITTED)

-- T1 (Session A):
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- balance is now 500, but NOT yet committed

-- T2 (Session B), concurrent, reads the invalid intermediate state:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_id = 1;  -- reads 500 (dirty!)
COMMIT;
-- T2 worked with a value that never finally existed

-- T1 rolls back:
ROLLBACK;
-- balance returns to its original value
-- T2 remains based on the wrong, never-committed intermediate value

3. Non-repeatable read: the same value, twice different

A non-repeatable read occurs when a transaction reads the same row twice and, between the two reads, another transaction changes that row and commits. The reading transaction then sees two different, individually correct values within its own still-running transaction, which can lead to inconsistent calculations if both values are used within the same business logic.

-- Non-repeatable read timeline (possible under READ COMMITTED)

-- T1 (Session A):
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT price FROM products WHERE product_id = 42;  -- returns 19.99

-- T2 (Session B), commits in the meantime:
BEGIN;
UPDATE products SET price = 24.99 WHERE product_id = 42;
COMMIT;

-- T1, same transaction, second query of the same row:
SELECT price FROM products WHERE product_id = 42;  -- now returns 24.99
COMMIT;
-- Both prices were correct at their respective point in time,
-- but contradictory within a single transaction

4. Phantom read: new rows appearing mid-transaction

A phantom read occurs when a transaction runs a range query twice and, between the two executions, another transaction inserts new rows that match the filter condition and suddenly appear the second time around. Unlike a non-repeatable read, this is not about changed values of an existing row but about additional rows that were not there before.

-- Phantom read timeline (possible under REPEATABLE READ
-- per the strict SQL standard; in PostgreSQL/InnoDB largely
-- prevented in practice via MVCC and next-key locks respectively)

-- T1 (Session A):
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- returns 12

-- T2 (Session B), inserts a new matching row and commits:
BEGIN;
INSERT INTO orders (order_id, status, customer_id)
VALUES (9981, 'pending', 55);
COMMIT;

-- T1, same transaction, repeated range query:
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- under a strict SQL standard interpretation this could return 13
-- PostgreSQL/InnoDB still return 12 due to snapshot consistency
COMMIT;

5. Lost update: the fourth, often overlooked anomaly

Besides the three standard anomalies of the SQL standard, there is the lost update anomaly, which occurs particularly often in practice but is less frequently named explicitly. Two transactions read the same value, independently compute a new value based on the value they read, and write it back. The second write completely overwrites the first, without the first change ever taking effect, even though both transactions committed successfully.

-- Lost update timeline (possible under READ COMMITTED,
-- usually prevented by REPEATABLE READ with snapshot isolation,
-- but this needs to be verified per database)

-- T1 (Session A):
BEGIN;
SELECT stock FROM inventory WHERE product_id = 42;  -- reads 10

-- T2 (Session B), reads the same starting value, concurrently:
BEGIN;
SELECT stock FROM inventory WHERE product_id = 42;  -- also reads 10
UPDATE inventory SET stock = 10 - 3 WHERE product_id = 42;  -- writes 7
COMMIT;

-- T1 independently computes based on the originally read value of 10:
UPDATE inventory SET stock = 10 - 5 WHERE product_id = 42;  -- writes 5
COMMIT;
-- Final value: 5, even though 3 plus 5 equals 8 units that
-- should have been deducted, T2's change was completely lost

6. Which isolation level prevents which anomaly

The four isolation levels of the SQL standard exclude these anomalies step by step, though the practical implementation per database is sometimes stricter than the standard requires. The table below summarizes which anomaly is still possible under which isolation level according to the standard.

This table describes the minimum requirement of the SQL standard, not necessarily the actual behavior of every database. MySQL/InnoDB largely prevents phantom reads already under REPEATABLE READ through next-key locks, PostgreSQL also prevents them through its snapshot isolation, but detects a serialization failure instead on genuine write conflicts. For a specific application, the documented behavior of the database in use always counts, not just the standard's table.

7. Reproducing and testing anomalies in your own application

Concurrency anomalies like dirty read or phantom read can be reproduced deliberately by opening two parallel database sessions, either manually or in a test script, and interleaving the statements in the correct order. This is the most reliable way to verify the actual behavior of your own database under the configured isolation level, instead of relying solely on documentation.

-- Manual reproduction test with two psql sessions (PostgreSQL)
-- Terminal 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM inventory WHERE product_id = 42;

-- Terminal 2, while Terminal 1 stays open:
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
COMMIT;

-- Back in Terminal 1, repeat the same query:
SELECT * FROM inventory WHERE product_id = 42;
-- Difference from the first SELECT shows a non-repeatable read
COMMIT;

-- For automated tests: open two DB connections in the same test
-- script, force statements into the desired order via explicit
-- synchronization points (barriers, locks)

8. Practical consequences from real life

A non-repeatable read in a bank report can cause a statement to show two different balances for the same point in time within a single generation run, if a booking commits exactly while the report is being generated. A phantom read in an inventory management system can cause an availability check to count a certain number of free slots while new reservations are being inserted concurrently, resulting in more slots being allocated in the end than actually exist.

The lost update anomaly is particularly insidious in practice because it does not raise any error, both involved transactions commit successfully. A typical example is a shopping cart system where two concurrent requests read the same stock level, both independently compute a reduction, and the second write silently overwrites the first. The stock level appears correct but is actually too high, which only surfaces during a later inventory count.

9. Strategies for avoiding these anomalies

The most obvious strategy against all three standard anomalies is choosing a sufficiently strong isolation level, as described in the deep-dive article on isolation levels. REPEATABLE READ or SERIALIZABLE systematically exclude most of these problems, but cost throughput in return and, under SERIALIZABLE, additionally require retry logic for serialization failures in application code.

A complementary, often more practical strategy is targeted row-level locking: pessimistic locking with SELECT FOR UPDATE prevents lost updates and non-repeatable reads for exactly the affected rows, without raising the isolation level of the entire transaction. Optimistic locking with a version column detects lost updates after the fact at write time and lets the application react in a targeted way instead of locking preemptively across the board. Both approaches are covered in detail in the deep-dive articles on pessimistic and optimistic locking.

10. Summary

Dirty read, non-repeatable read, and phantom read are the three concurrency anomalies the SQL standard uses to define isolation levels, complemented by the practically relevant lost update anomaly. Each arises from a specific overlap of two transactions: invalid intermediate values for dirty reads, contradictory values of the same row for non-repeatable reads, additional rows for phantom reads, and lost writes for lost updates.

Anyone who has understood these anomalies through concrete two-transaction timelines can choose their own isolation level based on the actually tolerable risks, instead of defaulting to the strongest or weakest level across the board. Reproduction tests with two parallel sessions are the most reliable way to verify a database's documented behavior against its actual behavior under load.

11. FAQ: Dirty Read, Phantom Read, Non-Repeatable Read Compared