Concurrency Anomalies
When two transactions interleave, five specific things can go wrong — lost update, dirty read, non-repeatable read, phantom read, write skew — and each has a name because each has a different cause and a different fix.
Lost update
Two transactions read balance = 500. A subtracts 100 and writes 400. B subtracts 200 and writes 300. Final balance: 300. Correct answer: 200. A’s update is gone, and nothing errored. The cause is a read-modify-write in application code: the value was read, arithmetic was done outside the database, and a computed result was written back, blind to what happened in between.
Fixes: do the arithmetic in the statement (SET balance = balance - 100); take a lock at read time (SELECT … FOR UPDATE); or use a version column and write only if it is unchanged (optimistic locking). At Repeatable Read, PostgreSQL detects the second write and aborts it — but at Read Committed, the default, it does not.
Dirty read
Reading another transaction’s uncommitted change. If that transaction rolls back, you acted on a value that never existed. Only possible at Read Uncommitted, which PostgreSQL does not implement — it silently upgrades to Read Committed, because MVCC never exposes an uncommitted row version. On engines that allow it, never use it for anything that drives a decision.
Non-repeatable read
Read a row, someone else commits a change to it, read it again in the same transaction and get a different value. Harmless for a single lookup; wrong for a report that reads the same data twice and expects the numbers to reconcile. Prevented by Repeatable Read, which gives the transaction one snapshot for its whole life.
Phantom read
Run a range query, someone else *inserts* a row inside that range and commits, run it again and a new row appears. Row locks cannot prevent it — the row did not exist to lock. The SQL standard allows phantoms at Repeatable Read and forbids them only at Serializable; PostgreSQL’s snapshot-based Repeatable Read prevents them anyway. Acting on the *absence* of rows ("insert if none exists") still needs Serializable or a unique constraint.
Write skew
Two transactions each read a shared condition, each find it satisfied, and each write a *different* row in a way that jointly breaks the condition. Two doctors both see two on call, both take themselves off call, nobody is on call. No row was written twice, so no lock and no first-writer-wins rule fires. Snapshot isolation cannot see it. Only Serializable — which tracks read/write dependencies between transactions — detects it and aborts one.
The alternative to Serializable is to make the writes collide on purpose: lock a single row that represents the shared condition (SELECT … FROM shifts WHERE id = ? FOR UPDATE) so the second transaction has to wait and re-check.
Key points
- Lost update: read-modify-write races. Fix with atomic statements, FOR UPDATE, or a version check.
- Dirty read: impossible in PostgreSQL. Non-repeatable and phantom: prevented by one snapshot per transaction.
- Write skew: different rows, shared condition. Only Serializable or a deliberate lock catches it.
- Every anomaly has a name because it has a distinct cause and fix.
Five concurrency anomalies
Two transactions read the same row, both compute a new value from what they read, and both write. The second write silently overwrites the first, so one update is simply gone.
Prevented by: Repeatable Read (Postgres aborts the second writer) · Serializable · or an explicit lock at Read Committed
| t | Transaction A | Transaction B |
|---|---|---|
| 1 | BEGIN | BEGIN |
| 2 | SELECT balance FROM accounts WHERE id=1; -- reads 500 | |
| 3 | SELECT balance FROM accounts WHERE id=1; -- reads 500 | |
| 4 | UPDATE accounts SET balance = 400 WHERE id=1; | |
| 5 | COMMIT; | |
| 6 | UPDATE accounts SET balance = 300 WHERE id=1; | |
| 7 | COMMIT; |
When to use — and when not
- Any code that reads and then writes based on what it read.
- Any invariant that spans more than one row.
- Do not reach for Serializable everywhere — it aborts under contention and requires a retry loop. Match the level to the anomaly you actually face.
Failure modes
- Application-side arithmetic on a value read moments earlier.
- Checking a condition and writing a different row than the one you checked.
- Assuming Repeatable Read prevents write skew.
See how this works internally →
Descend one layer: the same topic explained from the machinery up.