Transactions & Isolation

SYSTEM DESIGN SERIES  ·  TOPIC 13 OF 23+  ·  part of the 12 → 13 → 14 chain
1. The Lost Update, Live
Same two transactions, same starting balance - protected or not (looping)
No isolation: both transactions read 100 before either writes
No isolation: the second write overwrites the first - one update vanishes
No isolation: balance ends at 70. It should be 120. No error was ever thrown
Isolated: the second transaction waits for the first to finish
Isolated: it reads the updated value, not the stale one
Isolated: balance ends at 120 - correct, because nobody worked from stale data
2. ACID - What a Transaction Promises
Four separate guarantees, often lumped into one word
  • Atomicity - all its writes happen, or none do
  • Consistency - moves the DB from one valid state to another, by its own rules (a different "C" than CAP's, topic 12)
  • Isolation - concurrent transactions don't see each other's half-finished work
  • Durability - once committed, it survives a crash (the WAL, topic 09)
🧾 A transaction is a promise: either the whole receipt goes through, cleanly, or none of it does.
3. The Four Classic Anomalies
What can go wrong when transactions overlap
Dirty Read
Reading another transaction's uncommitted change - one that might still roll back.
Non-Repeatable Read
Reading the same row twice, getting two different values, because someone else committed in between.
Phantom Read
Re-running the same query, getting a different set of rows - not just different values.
Lost Update
Two writers, one read each, one write silently overwrites the other's work.
4. Isolation Levels - What Each One Stops
Stricter levels prevent more anomalies, at a real cost in concurrency
📈 Going down this list, each level fixes one more anomaly - and gives up a little more concurrency to do it.
5. Locking vs MVCC
Locking (Pessimistic)
  • Grab a lock before touching a row
  • Everyone else waits their turn
  • Simple to reason about, but can stall the whole line
VS
MVCC (Optimistic)
  • Everyone reads their own consistent snapshot
  • Writers don't block readers
  • Conflicts caught at commit time, not up front
📸 Analogy: Locking is one key to the meeting room. MVCC hands everyone a photo of the room as it was when they walked in - you can look all you want, it just won't change under you.
6. What Real Databases Default To
Almost nobody ships at Serializable by default
PostgreSQL
Read Committed
MySQL / InnoDB
Repeatable Read
Oracle
Read Committed
SQL Server
Read Committed
Defaults are a compromise raise the level yourself when the data actually needs it
The default protects the common case, not your case. Money, inventory, and anything with a read-modify-write pattern usually deserves a closer look.
7. Choosing Your Isolation Level
Go stricter when:
✔ Money, balances, inventory counts
✔ Read-modify-write patterns on shared rows
✔ A wrong answer is worse than a slow one
Isolation Trades Concurrency for Correctness - Spend It Where It's Needed
Stay looser when:
✔ Mostly independent reads and writes
✔ Occasional staleness is genuinely fine
✔ Throughput matters more than perfect ordering
💡 Once one database genuinely isn't enough, you split it - and now these same guarantees span machines. That's Sharding - topic 14, next.