System Design HLD-06.04
Isolation levels and anomalies
Dirty reads, non-repeatable reads, phantoms, lost updates and write skew; what each isolation level prevents; MVCC; and SELECT FOR UPDATE.
The big idea
The I in ACID promises that concurrent transactions do not trip over each other. Taken literally, that would mean running them one at a time, which is far too slow for a busy database. So databases offer isolation levelsA setting that controls which effects of concurrently running transactions one transaction may observe, trading strictness for performance.: weaker levels allow more concurrency but let certain strange outcomes, called anomalies, through. Most databases default to a weaker level than serializable, which means many applications are exposed to some anomalies without their developers knowing.
Each anomaly is a specific interleaving that produces a wrong answer: reading data another transaction later rolled back, seeing a value change mid-transaction, two increments where one is lost, or two checks that are each valid but together break a rule. Knowing them by name lets you recognize the bug in a design review and pick the level, or the explicit lock, that prevents it.
Think of two people editing the same shared shopping list from their phones. Depending on the app's rules, you may see the other person's half-typed item, see an item vanish while you read, both "buy the last milk", or both decide someone else will buy bread.
How it works
Step 1: The anomalies
- Dirty read: reading another transaction's uncommitted change, which may then be rolled back.
- Non-repeatable read: reading the same row twice in one transaction and getting different values, because another transaction committed in between.
- Phantom read: running the same query twice and getting a different set of rows, because another transaction inserted or deleted matching rows.
- Lost update: two transactions read a value, both compute a new value from it, and the second write silently overwrites the first.
- Write skew: two transactions read overlapping data, each checks a rule that holds, and each writes something different; together they break the rule.
Write skew is the subtle one:
Step 2: The levels
| Level | Dirty read | Non-repeatable | Phantom | Lost update | Write skew |
|---|---|---|---|---|---|
| Read uncommitted | possible | possible | possible | possible | possible |
| Read committed | prevented | possible | possible | possible | possible |
| Snapshot / repeatable read | prevented | prevented | prevented* | prevented* | possible |
| Serializable | prevented | prevented | prevented | prevented | prevented |
*As implemented in PostgreSQL's repeatable read, which is snapshot isolation. The SQL standard's definitions are looser, and databases differ: MySQL's repeatable read, for example, can still lose updates for a plain read-then-write. Always check what your database actually does.
Read committed is the default in PostgreSQL, Oracle and SQL Server. Repeatable read is MySQL InnoDB's default.
Step 3: MVCC, how snapshots work
Most databases implement read committed and snapshot isolation with MVCCMulti-version concurrency control: keeping several versions of each row so readers see a consistent snapshot without blocking writers.. An update does not overwrite a row; it writes a new version tagged with the transaction that created it. Each transaction reads the versions that were committed as of its snapshot, so readers never block writers and writers never block readers. Old versions are cleaned up once no transaction can still see them.
Try it
Try this:
- Lost update at read committed: both transactions sell an item, stock ends at 9 instead of 8. Switch to snapshot isolation: the second writer gets a serialization error and must retry.
- Write skew at snapshot isolation: both doctors leave. Switch to serializable: one transaction is aborted.
- Dirty read: only read uncommitted shows it. Most databases do not even implement that level.
Going deeper
Fixing anomalies without serializable
Serializable is the simplest correct choice, and modern implementations (PostgreSQL's serializable snapshot isolation) are reasonably fast, but they abort more transactions, so the application must retry. Alternatives at lower levels:
- Atomic updates:
UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0does the read and write in one statement, preventing lost updates. - Explicit locks:
SELECT ... FOR UPDATElocks the rows you read, so a concurrent transaction waits instead of reading the same old value. - Constraints: a unique index prevents two bookings of the same seat, whatever the isolation level.
- Materializing the conflict: for write skew, make both transactions update a common row (a per-shift counter), so the conflict becomes a write-write conflict that snapshot isolation detects.
Distributed systems have isolation too
These same anomalies reappear, with new names, when data is replicated or partitioned across machines. Consistency models in PACELC and consistency models are the distributed cousins of isolation levels.
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Read committed | Most web workloads with short transactions and atomic updates | Read-modify-write logic and multi-row checks, without explicit locks |
| Snapshot isolation | Consistent reports and read-modify-write with retry on conflict | Write skew across different rows |
| Serializable | Correctness by default for complex invariants | Throughput under contention, and it requires retry logic |
Common mistakes
- Assuming your database's default level is serializable. It almost never is.
- Read-then-write logic in application code (
read stock, subtract, write) without an atomic update or lock. - Using serializable without retrying aborted transactions.
- Trusting the level's name: "repeatable read" means different things in different databases.
Check yourself
-
Two transactions each read stock 10 and write 9. What is this anomaly?
Show answer
B. Lost update Both read the same value and the second write overwrites the first; one decrement is lost.
-
Which level is the default in PostgreSQL?
Show answer
B. Read committed PostgreSQL, Oracle and SQL Server default to read committed.
-
Why does snapshot isolation allow write skew?
Show answer
B. The transactions write different rows, so there is no write-write conflict to detect Each check is valid in its snapshot; the conflict is between a read and another's write, which SI does not track.
-
Which statement avoids a lost update at read committed?
Show answer
B. UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0 The read and write happen atomically inside one statement.
Recap
- Isolation levels trade strictness for concurrency; weaker levels allow anomalies.
- Anomalies: dirty read, non-repeatable read, phantom, lost update, write skew.
- Read committed is the common default; snapshot isolation still allows write skew.
- MVCC keeps row versions so readers and writers do not block each other.
- Use atomic updates, SELECT FOR UPDATE, constraints or serializable with retries.
Next up
- Crack the system design round learning path