Transaction anomalies

Two requests arrive at the same moment. Both read a balance of $100, both subtract $80, and both write back $20. The user withdrew $160 from a $100 account, and the database raised no error. Every statement ran correctly. The bug came from how the two transactions interleaved.

A transaction groups reads and writes into one unit. It either commits as a whole or rolls back as a whole. Its guarantees are called ACID:

• Atomicity: all of the writes happen, or none do.
• Consistency: the data still follows your rules, as far as constraints can enforce them.
• Isolation: concurrent transactions do not see each other's unfinished work. This is the part you can tune.
• Durability: once a commit returns, the data survives a crash.

Isolation is where the anomalies come from. In the examples below, T1: and T2: mark which session runs each line. They are not SQL labels.

Dirty read

T2 sees a value that T1 wrote but has not committed yet. T1 may then roll it back.

-- accounts: (id=1, balance=100)
T1: BEGIN;
T1: UPDATE accounts SET balance = 0 WHERE id = 1;
T2: SELECT balance FROM accounts WHERE id = 1;   -- a dirty read would return 0
T1: ROLLBACK;                                    -- that 0 never existed

Postgres never allows dirty reads, even if you ask for READ UNCOMMITTED. A related problem, the dirty write (overwriting another transaction's uncommitted write), is blocked at every level by row locks.

Non-repeatable read

A row read twice in one transaction returns two different values, because another transaction committed in between.

Take two accounts with 500 each. The total is always 1,000.

-- accounts: (1, 500), (2, 500)
T1: BEGIN;
T1: SELECT balance FROM accounts WHERE id = 1;   -- 500
T2: BEGIN; UPDATE accounts SET balance = 400 WHERE id = 1;
T2: UPDATE accounts SET balance = 600 WHERE id = 2; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 2;   -- 600
T1: -- the two reads add up to 1,100, a total that never existed

This matters for backups, reports and any multi-query read that must be self-consistent.

Phantom read

A query with a condition returns a different set of rows when it runs again. Another transaction inserted or deleted a row that matches.

T1: BEGIN;
T1: SELECT count(*) FROM bookings WHERE room = 7 AND day = '2026-10-20';  -- 0
T2: INSERT INTO bookings (room, day) VALUES (7, '2026-10-20'); COMMIT;
T1: SELECT count(*) FROM bookings WHERE room = 7 AND day = '2026-10-20';  -- 1

Phantoms are harder to prevent than non-repeatable reads. You can lock a row you have already read, but you cannot lock a row that does not exist yet.

Lost update

Two transactions read a value, change it in application code, and write it back. One write silently overwrites the other.

-- counters: (page='home', views=10)
T1: SELECT views FROM counters WHERE page = 'home';         -- 10
T2: SELECT views FROM counters WHERE page = 'home';         -- 10
T1: UPDATE counters SET views = 11 WHERE page = 'home'; COMMIT;
T2: UPDATE counters SET views = 11 WHERE page = 'home'; COMMIT;
-- two views were served, but the counter went up by only one

This is the withdrawal bug from the opening. It is the most common concurrency bug in web applications, because ORMs encourage exactly this read-modify-write shape. A subtler cousin, write skew, is covered in isolation levels.

Common mistakes

• Assuming the default prevents lost updates. Most default levels allow them. The read-modify-write in your code is the problem.
• Reading uncommitted data on purpose. Postgres hides it, but other databases may not, and a rollback can erase it.
• Expecting consistency to be automatic. The database only enforces rules you express as constraints, such as NOT NULL, CHECK, UNIQUE and foreign keys.
• Blaming the database for a phantom in one query. Under weak isolation, a repeated query with a condition can legitimately return different rows.

Check yourself

1. Two sales each read a stock of 1 and each sell it. Which anomaly is this, and what fixes it? A lost update. An atomic UPDATE ... WHERE stock >= 1 or a SELECT ... FOR UPDATE fixes it.
2. A report reads account A, then account B, and the totals do not add up. Which anomaly is it? A non-repeatable read, sometimes called read skew. The report saw two different committed states.

Key takeaways

• Atomicity and durability come from the log. Isolation is the part you tune.
• Learn the anomalies as timelines: dirty read, non-repeatable read, phantom read and lost update.
• A rollback means no other transaction should have relied on the value.
• Read-modify-write in application code is the most common source of lost updates.

Further learning

Martin Kleppmann's Strange Loop talk covers what ACID actually means and why weak isolation causes real bugs.

Next, read isolation levels to see which of these anomalies each level allows.