Chapter 76·Advanced·11 min read
Isolation Levels & Locking: Concurrency Without Chaos
A deep, plain-English guide to transaction isolation — the anomalies (dirty reads, non-repeatable reads, phantoms), the four isolation levels, how locking and MVCC enforce them, and how to choose the right level without deadlocking your app.
July 5, 2026
Isolation is the ACID property with a dial. Turn it to maximum and transactions behave as if each ran completely alone — but throughput drops as they wait on each other. Loosen it and you gain concurrency, at the price of specific, well-defined bugs creeping in. This chapter is about choosing that setting knowingly, because the defaults surprise people and the failures are subtle.
Why isolation is a spectrum
Perfect isolation would run every transaction one at a time, in sequence. Correct, but catastrophically slow — a busy database handles thousands of concurrent transactions. So databases let transactions overlap and offer isolation levels: named points on the tradeoff between strictness (fewer anomalies) and concurrency (more throughput). To choose, you first need to know the anomalies you're trading away.
The three classic anomalies
Each is a concrete way concurrent transactions can mislead one another.
Dirty read — you read data another transaction has written but not yet committed. If that transaction rolls back, you acted on a value that never officially existed.
Non-repeatable read — you read a row, and when you read it again in the same transaction, its value has changed because another transaction committed an update in between. The same query, two answers.
Phantom read — you run a query returning a set of rows (say, "all orders over $100"), and on re-running it, new rows have appeared because another transaction inserted matching ones. The individual rows didn't change; the set did.
The four isolation levels
The SQL standard defines four levels, each blocking more anomalies than the last:
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | possible | possible | possible |
| Read Committed | prevented | possible | possible |
| Repeatable Read | prevented | prevented | possible* |
| Serializable | prevented | prevented | prevented |
Read Committed is the default in many databases (including PostgreSQL): you never see uncommitted data, but the same query can change between reads. Serializable is the gold standard — transactions behave exactly as if run one after another — but it's the most likely to force waits or abort-and-retry. (*Some databases, like PostgreSQL, block phantoms at Repeatable Read too, going beyond the standard.)
How databases enforce isolation
Two broad mechanisms, and the difference shapes how your app behaves under load.
Locking. A transaction takes locks on the rows (or ranges) it reads and writes; conflicting transactions wait for the locks to release. Simple and strict, but readers can block writers and vice versa, throttling concurrency.
MVCC — Multi-Version Concurrency Control. Instead of locking readers out, the database keeps multiple versions of each row. Each transaction reads from a consistent snapshot taken at its start, so readers never block writers and writers never block readers. This is how PostgreSQL, Oracle, and others deliver strong isolation with high concurrency — the reason a long analytical read doesn't freeze your writes.
Writes still conflict — two transactions updating the same row must serialize — but the pervasive reader/writer blocking of pure locking disappears.
Deadlocks: the cost of holding locks
When transactions do take locks, they can trap each other. Transaction 1 locks row A then wants row B; transaction 2 locks row B then wants row A. Each waits for the other, forever — a deadlock.
Databases detect the cycle and abort one transaction with a deadlock error. This is not a failure you can fully prevent — it's a normal condition to handle. Two defenses: acquire locks in a consistent order everywhere in your code (so cycles can't form), and wrap transactions in retry logic, since a deadlock-aborted transaction usually succeeds on a second attempt.
Choosing a level
A pragmatic default: stay on Read Committed unless you have a reason to climb. Reach for Repeatable Read or Serializable when a transaction reads a value and later depends on it not having changed — balance checks before a withdrawal, inventory counts before a sale, any "check then act" logic. And whenever you use locking-based strictness, assume deadlocks will happen and retry. Isolation is a knob you turn toward your correctness needs, paying only as much concurrency as those needs require.
Recap
- Isolation level trades strictness for concurrency — a real dial with real consequences.
- The classic anomalies are dirty reads, non-repeatable reads, and phantoms; your level decides which are possible.
- The four levels — Read Uncommitted → Read Committed → Repeatable Read → Serializable — block progressively more anomalies.
- Databases enforce isolation with locking or MVCC; MVCC lets readers and writers avoid blocking each other via snapshots.
- Deadlocks are a normal outcome of locking — order your locks consistently and retry on abort.
- Default to Read Committed; climb only where "check then act" correctness demands it.
We've covered how data behaves under concurrency. Now step back to how it should be shaped in the first place. Continue to Normalization vs Denormalization.