Transactions, Isolation & MVCC
Intermediate · 20 min read · High-Level Design, Storing Data
ACID in practice, isolation anomalies, MVCC, locking and optimistic concurrency.
The moment two users can touch the same data at the same time, correctness depends on concurrency control. Can two people book the last seat? Can a transfer leave money in neither account? Can a report see half of an update?
What a transaction gives you
A transaction groups several reads and writes into one logical unit with ACID guarantees:
The anomalies
Isolation levels are defined by which anomalies they allow. Learn these with an example each.
Isolation levels
Step through the three anomalies that matter most in interviews, each followed by its fix. The top panel shows every version of each row, which version each transaction sees, and who holds a lock.
How databases implement isolation
Instead of overwriting a row in place, the database keeps multiple versions, each tagged with the transaction ID that created (and later deleted) it. Each transaction reads from a consistent snapshot: it sees only versions committed before its snapshot was taken.
Practical tools for application code
You won't always run at Serializable. These techniques solve specific anomalies at lower cost:
Transactions in distributed and NoSQL systems
Interview questions
- Two users click 'Book' on the last seat at the same moment. Walk through how you prevent a double booking.
- What is write skew and why doesn't snapshot isolation prevent it?
- What's MVCC and why do most databases use it?
- When would you choose optimistic over pessimistic locking?
Practice
Write your answer before opening the suggested one. If you mark it "Partly" or "Missed it", it comes back in your review deck.
Further reading
Key takeaways
- ACID: atomicity handles failures, isolation handles concurrency, durability comes from the WAL.
- Know the anomalies: dirty reads, non-repeatable reads, lost updates, write skew, phantoms.
- Read Committed is the common default; snapshot isolation adds consistent reads; only Serializable prevents write skew.
- MVCC lets readers and writers proceed without blocking; 2PL and SSI provide serializability by locking or by detecting conflicts.
- In designs, name the exact mechanism at each contention point: atomic update, conditional write, unique constraint, FOR UPDATE, or serializable retry.