Concurrency bugs are the worst kind: rare, unreproducible on your machine, and catastrophic when they land (double-charged customers, oversold inventory, vanished balances). Transactions are the database's answer — but using them correctly requires understanding what they actually promise between concurrent operations. That's isolation levels, explained here through the anomalies each one permits.
What a transaction guarantees#
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK on any error
Four promises, abbreviated ACID:
- Atomicity: all statements succeed together or none apply — no half-transfers ever visible
- Consistency: constraints hold before and after (a transaction that violates them fails)
- Isolation: concurrent transactions don't see each other's intermediate states
- Durability: once
COMMITreturns, the data survives crashes
Atomicity and durability are absolute. Isolation is a dial — full isolation costs performance, so Postgres lets you choose how much anomaly risk to accept. The four isolation levels are best understood not by their names but by which weirdness each allows.
The anomalies (what can go wrong between transactions)#
Dirty read: reading another transaction's uncommitted changes. If it rolls back, you acted on data that never existed. → Prevented at every level; impossible in Postgres entirely.
Non-repeatable read: you read a row; another transaction updates and commits; you read the same row again and get a different value. Your own transaction contradicts itself mid-flight.
Phantom read: same as above but for sets — your count of matching rows changes because another transaction inserted/deleted qualifying rows.
Serialization anomaly: each transaction is individually consistent, but their interleaved result couldn't occur in any sequential order — the classic lost-update: two sessions read the same balance, both add 100, both write 1100 instead of 1200.
The four levels mapped to anomalies#
| Level | Dirty read | Non-repeatable | Phantom | Serialization |
|---|---|---|---|---|
| READ UNCOMMITTED | possible* | possible | possible | possible |
| READ COMMITTED (default) | prevented | possible | possible | possible |
| REPEATABLE READ | prevented | prevented | possible*† | possible |
| SERIALIZABLE | prevented | prevented | prevented | prevented |
* Not in Postgres specifically — it never permits dirty reads, so its lowest level is effectively stricter than the SQL standard imagines. † Postgres's REPEATABLE READ also prevents phantoms — stronger than the standard requires.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- concurrent conflicting commits make this fail:
-- ERROR: could not serialize access due to concurrent update
ROLLBACK; -- or retry
Choosing: the default is usually right#
READ COMMITTED is Postgres's default for good reason: each statement sees a fresh snapshot, anomalies require specific read-modify-write interleavings to matter, and throughput stays high. Start here.
Escalate deliberately when you detect an actual race:
- Lost updates on single rows → often best solved without isolation changes:
UPDATE accounts SET balance = balance + 100
WHERE id = 1; -- atomic: reads and writes in one statement
or optimistic locking with version checks:
UPDATE docs SET body = $new, version = version + 1
WHERE id = $id AND version = $seen_version;
-- zero rows updated = someone else won; retry or inform
- Multi-statement invariants across rows ("balance total must never go negative," booking systems) → REPEATABLE READ or SERIALIZABLE, plus retry logic, because higher isolation trades failures-you-detect for failures-you-miss
The professional pattern worth internalizing: serialization failures aren't errors — they're the database telling you two operations genuinely conflicted. Wrap such transactions in retry-with-backoff (the same discipline as any contention) rather than lowering isolation to hide them.
Interview-ready summary#
Transactions give atomic all-or-nothing execution; isolation levels trade anomaly exposure for concurrency. Know the three anomalies by scenario (reread differs / row set shifts / lost update), know Postgres's default (READ COMMITTED), and know that correctness under races usually comes from atomic statements + optimistic retries more often than from maxing out isolation. That last sentence is what separates people who've run production databases from people who've memorized the table above.
Related: index strategy · query optimization · system design framework
