“All or nothing” sounds simple. Delivering it while a thousand other transactions run concurrently and the power might fail mid-write is one of the great achievements of computer science.
Prerequisites: Storage Engines, Concurrency Basics Time to read: ~22 minutes
Transfer $100 from Ayesha to Bilal:
UPDATE accounts SET balance = balance - 100 WHERE id = 'ayesha';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bilal';
Everything that can go wrong between those two lines:
A transaction is the database’s promise that a group of operations behaves as one indivisible operation despite all of the above.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'ayesha';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bilal';
COMMIT;
Four guarantees, and they’re less uniform than the acronym suggests.
All operations succeed, or none do. If anything fails, everything rolls back as if it never happened.
🚨 The word is badly chosen. Atomicity here has nothing to do with concurrency (that’s isolation). It’s about abortability — the ability to safely give up partway through. Kleppmann argues it should have been called that, and he’s right.
How it works: the database writes an undo log alongside the WAL, recording the previous value of everything changed. Rollback replays the undo log backwards. Crash recovery does the same for uncommitted transactions found in the log.
Why you care in system design: atomicity is what lets you retry safely. Without it, a failed operation leaves partial state, and you have no idea what to do next.
🚨 The odd one out, and a common interview trip-up.
ACID’s “consistency” means the database enforces your declared invariants — foreign keys, unique
constraints, CHECK constraints, NOT NULL. A transaction takes the database from one valid state
to another valid state.
Note what this isn’t:
🎙️ Being able to say “the C in ACID is about constraint enforcement on a single database, which is a completely different thing from the C in CAP” is a small, specific, effective depth signal.
Concurrent transactions don’t interfere with each other. Ideally, the result is as if they ran one at a time.
This is the hardest property, and the one databases actually weaken by default for performance — which is why it gets its own chapter. → Isolation Levels
🚨 The default isolation level in Postgres, MySQL, Oracle, and SQL Server is not serializable. Most engineers assume their transactions are fully isolated. They aren’t. This is the single most important practical fact in this part of the guide.
Once committed, it survives — crashes, power loss, restarts.
How: the WAL is fsync‘d to disk before the commit is acknowledged. On restart, the log is
replayed.
📐 Durability is a spectrum, not a boolean, and this is where it gets interesting:
| Setting | Guarantee | Cost |
|---|---|---|
| No fsync (OS buffer only) | Survives process crash, not power loss | Fastest |
| fsync to local disk | Survives power loss on this machine | ~0.1–10 ms per commit |
| fsync + synchronous replica | Survives this machine dying entirely | + one network round trip |
| + replica in another AZ | Survives a datacenter failure | + a few ms |
| + replica in another region | Survives a region failure | + 100–200 ms |
⚖️ Every database exposes this as a knob: Postgres’s synchronous_commit, MySQL’s
innodb_flush_log_at_trx_commit, MongoDB’s write concern, Cassandra’s consistency level. “Is this
durable?” is really “durable against which failure?” — and answering that precisely is a strong
interview move.
🚨 And note: a single machine’s fsync is not enough for anything important. Disks fail, machines die, entire racks lose power. Real durability means the data is on more than one machine before you acknowledge. → Replication
Two families, and knowing the difference matters.
Assume conflict is likely. Acquire a lock before touching data; others wait.
BEGIN;
SELECT balance FROM accounts WHERE id = 'ayesha' FOR UPDATE; -- exclusive lock
UPDATE accounts SET balance = balance - 100 WHERE id = 'ayesha';
COMMIT; -- lock released
Two-phase locking (2PL) is the classic protocol: acquire all locks (growing phase), then release them all at commit (shrinking phase). It gives you serializability.
❌ Contention. Hot rows serialize everything. A counter updated by every request becomes a bottleneck regardless of how many servers you add. ❌ Deadlock. Transaction A holds row 1 and wants row 2; B holds row 2 and wants row 1. Both wait forever.
Databases detect deadlocks (usually with a wait-for graph) and kill one transaction with an error.
🚨 Your application must handle deadlock errors by retrying — they’re a normal condition, not a
bug. Code that doesn’t retry on 40001 / deadlock detected will fail intermittently in production.
Deadlock prevention: always acquire locks in a consistent global order (e.g. always the lower account ID first). This is the same rule as in thread-level concurrency.
Assume conflict is rare. Don’t lock — check at commit time whether anyone else changed the data.
-- Read
SELECT balance, version FROM accounts WHERE id = 'ayesha'; -- balance=500, version=7
-- Write, conditional on nothing having changed
UPDATE accounts SET balance = 400, version = 8
WHERE id = 'ayesha' AND version = 7;
-- 0 rows updated? Someone else won. Re-read and retry.
✅ No lock contention; readers never block. ❌ Wasted work on conflict; performs badly under high contention (constant retries).
⚖️ The rule: optimistic for low-contention, pessimistic for high-contention. A flash sale on one product is high contention — optimistic locking would produce a retry storm. Editing your own profile is low contention — optimistic is ideal.
🎙️ “I’d use optimistic concurrency with a version column for profile edits — conflicts are rare and readers never block. For inventory decrements during a flash sale I’d use a pessimistic row lock or a conditional atomic update, because at high contention optimistic retries would thrash.”
Multi-Version Concurrency Control is what Postgres, MySQL/InnoDB, and Oracle really use, and it’s worth understanding because it explains a lot of their behaviour.
Instead of overwriting a row, keep multiple versions, each tagged with the transaction that created it. Each transaction sees a snapshot — the versions that were committed when it started.
🚨 The headline property: readers never block writers, and writers never block readers. A long-running report doesn’t lock out updates; an update doesn’t stall the report. This is why MVCC won.
The costs, which show up as real operational issues:
VACUUM to reclaim dead tuples; InnoDB has an undo
log and purge thread. Autovacuum falling behind is behind a large share of Postgres performance
incidents.Everything above assumes one database. Once data spans machines, all of it gets harder.
| Situation | What’s available |
|---|---|
| Single node | Full ACID |
| Replicated (one leader) | ACID at the leader; replicas lag |
| Sharded, transaction within one shard | Full ACID |
| Sharded, across shards | 🚨 Two-phase commit or sagas |
| Across microservices | 🚨 Sagas — no ACID |
Two-phase commit (2PC) gets you atomicity across nodes: a coordinator asks everyone to prepare, then tells everyone to commit. It works, but it blocks — if the coordinator dies after the prepare phase, participants hold locks indefinitely, unable to decide. → Distributed Transactions
Sagas are the practical alternative: a sequence of local transactions, each with a compensating action to undo it. You get eventual consistency and no locks, but no isolation — intermediate states are visible. → Saga Pattern
🚨 This is why “we’ll shard” is a bigger decision than it looks. You’re not just splitting data; you’re giving up cross-shard transactions, and the replacement is a substantial amount of application logic. → Sharding
NewSQL (Spanner, CockroachDB, TiDB, YugabyteDB) provides distributed ACID transactions, using consensus plus careful clock handling. It’s real, and it costs latency — a cross-region transaction pays cross-region round trips.
Keep transactions short. 🚨 The most important rule. A transaction holds locks and blocks MVCC cleanup for its entire life.
# ❌ Terrible: holds a transaction across a network call to a third party
with db.transaction():
order = create_order()
charge_card(order) # 800 ms external API call, holding locks the whole time
update_inventory(order)
# ✅ Better: transaction covers only database work
with db.transaction():
order = create_order(status='pending')
reserve_inventory(order)
charge_card(order) # outside the transaction
with db.transaction():
mark_order_paid(order)
The first version holds locks for nearly a second per order. Under load, the connection pool exhausts and everything stops.
Never hold a transaction across: an HTTP call, a message publish, a file upload, user input, or
a sleep.
Do the work in the database where you can. UPDATE accounts SET balance = balance - 100 is
atomic in one statement. Read-modify-write in application code needs a lock or a version check.
Handle retryable errors. Deadlocks and serialization failures are normal. Wrap transactions in a retry with backoff.
Watch connection pool interaction. An open transaction holds a connection. Long transactions × concurrency = pool exhaustion. → Connection Pooling
| Decision | Gain | Cost |
|---|---|---|
| Transactions | Correctness under concurrency and failure | Locks, contention, reduced throughput |
| Pessimistic locking | Correct under high contention | Blocking, deadlocks, serialization |
| Optimistic locking | No blocking; great when conflicts are rare | Retry storms under contention |
| MVCC | Readers don’t block writers | Version bloat; vacuum burden; long transactions are toxic |
| Stronger durability (fsync + sync replica) | Survives more failures | Latency per commit |
| Distributed transactions (2PC) | Atomicity across nodes | Blocking, coordinator SPOF, slow |
| Sagas | No locks, works across services | No isolation; compensation logic; eventual consistency |
VACUUM doesn’t run for long enough, the database goes into emergency
read-only mode to avoid data loss. Several companies have had multi-hour outages from this. It’s the
clearest example of MVCC’s hidden maintenance cost.1. Create a lost update. Two concurrent sessions in Postgres:
-- Session 1 -- Session 2
BEGIN; BEGIN;
SELECT balance FROM accounts SELECT balance FROM accounts
WHERE id=1; -- 500 WHERE id=1; -- 500
UPDATE accounts SET balance = 400 UPDATE accounts SET balance = 400
WHERE id=1; WHERE id=1; -- blocks…
COMMIT; COMMIT; -- …then overwrites
-- Final balance: 400, not 300. One withdrawal vanished.
Now redo it with SELECT ... FOR UPDATE and watch it become correct.
2. Cause a deadlock deliberately. Session 1 updates row A then row B; session 2 updates row B
then row A. Postgres will detect it and kill one with deadlock detected. Then fix it by having both
sessions update rows in ID order — and notice that the fix is a convention, not a feature.
3. See MVCC bloat. Open a transaction and leave it idle. In another session, update the same table 100,000 times. Then check:
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
Dead tuples pile up and VACUUM can’t reclaim them while your idle transaction holds its snapshot.
This is the single most useful thing to have witnessed before running Postgres in production.
4. Measure durability’s cost.
SET synchronous_commit = off; -- then benchmark inserts
SET synchronous_commit = on; -- benchmark again
The throughput difference is exactly what you’re paying for durability.