Databases & Storage · core

Isolation Levels & MVCC

What each isolation level actually prevents, and how MVCC delivers snapshots without read locks.

databasestransactions

Mental model

An isolation level is a contract about which anomalies you are willing to see, not a dial labelled 'safety'. Read Committed still allows non-repeatable reads; Snapshot Isolation gives every transaction a consistent point-in-time view and still permits write skew, because two transactions can each read a state the other is about to invalidate. MVCC is the implementation trick that makes those snapshots cheap — writers create new versions instead of overwriting, so readers never block writers, at the cost of version storage and vacuum.

How to study Isolation Levels & MVCC

Begin by restating the mental model in your own words, then connect it to a concrete system you have built or operated. Name the mechanism, the constraint it addresses, and the trade-off it introduces. Use the linked roadmap and primary implementation references to check details, but close the source before writing your explanation. Retrieval is the learning step; rereading is only preparation.

Next, compare Isolation Levels & MVCC with Replication, Concurrency Design. Ask what changes in correctness, latency, resource use, operability, and failure recovery. Complete Detect write skew under snapshot isolation and preserve the command, input, output, and one failed attempt as evidence. Finish by explaining the idea without jargon to someone who has not studied the track.

Proof of understanding

Explain the mechanism from first principles and identify the state it reads or changes.
Give one situation where the concept is the right choice and one where it is not.
Predict a realistic failure mode before running the drill, then compare the prediction with evidence.
Connect the result to a roadmap or build artifact instead of treating the concept as isolated trivia.

Where it matters

Every 'why did this row change under me' incident, and the reason booking and inventory systems need explicit locking.

Common mistakes

Reading a vendor's level name as the standard's — Oracle and PostgreSQL 'Serializable' historically meant different guarantees
Assuming Snapshot Isolation prevents all anomalies; write skew survives it and needs SELECT FOR UPDATE or true serializability
Forgetting MVCC's cost: long-running readers pin old versions and bloat the table
Raising the isolation level to fix a bug whose real cause is a missing constraint

Learn from primary sources

Use the linked roadmap context and practice prompt.

Practice and explain it back

Detect write skew under snapshot isolation

Two transactions each read the same snapshot and then write DIFFERENT rows based on what they read — snapshot isolation permits this, and the invariant breaks. Implement wouldSkew(t1, t2) where each transaction is { reads: string[], writes: string[] }. Return true when they are a write-skew risk: their read sets overlap, at least one transaction writes a row the OTHER read, and they write disjoint rows. Return false otherwise.

Expected evidence: wouldSkew({reads:['a','b'],writes:['a']},{reads:['a','b'],writes:['b']}) -> true

Open the interactive drill →

Review prompts

Two on-call engineers each check 'is anyone else on duty?', both see one other person, and both go off duty. Snapshot isolation did not stop it. Why not, and what does?