Module 07 · Beyond the toy ~18 min · isolation & MVCC

MVCC & Isolation Levels

Module 6 made concurrency safe with locks — but at a price: a writer blocks every reader. Real databases like PostgreSQL let thousands of readers and writers run at once, and readers never block writers. The trick is to stop overwriting data and start versioning it. This is the deepest idea in modern databases.

📚 Hold this picture

A document with full version history, like a wiki or Google Doc. When someone edits a paragraph, the old text isn't destroyed — a new version is saved alongside it. Anyone who opened the page a moment ago keeps reading their version, undisturbed, while the editor writes a new one. Nobody waits for anybody. The database keeps a version history of every row, and each transaction reads the snapshot that was current when it began.

A note on scope. Our teaching engine from Modules 1–6 keeps things simple: it uses locks plus deferred writes (Module 5). It does not implement MVCC. This module steps beyond the toy to show how production databases — PostgreSQL, MySQL/InnoDB, Oracle, SQL Server — solve the same problem at scale. The mechanics below are how they work.

◆ The key insight

Multi-Version Concurrency Control (MVCC) never overwrites a row in place. It writes a new version and keeps the old one. Each transaction reads from a consistent snapshot, so reads never need locks — and the isolation level simply decides how that snapshot behaves.

Why should you care?

Isolation levels are where correct-looking code quietly breaks in production. "It worked in testing" but double-charges a customer under load; a report that doesn't add up because rows shifted mid-query. Picking the right level — and knowing what each one does and doesn't guarantee — is one of the highest-leverage skills a backend developer can have.

The problem MVCC solves

Recall the reader-writer lock from Module 6. It's correct, but look at what it costs when one slow writer shows up:

locking (Module 6)

writer holds the lock
   ├─ reader A  ⏳ blocked
   ├─ reader B  ⏳ blocked
   └─ reader C  ⏳ blocked
all reads freeze until
the writer is done

One writer stalls every reader of that row or table. Read-heavy workloads grind under contention.

MVCC

writer makes a NEW version
   ├─ reader A → reads old ✓
   ├─ reader B → reads old ✓
   └─ reader C → reads old ✓
nobody blocks; the writer
and readers run in parallel

Readers see the version valid for their snapshot; the writer builds the next one. No waiting in either direction.

The one rule that changes everything: never overwrite, always append a new version. Because the old version still exists, a reader that started earlier can keep reading it safely while the writer moves on. Locks coordinate access; MVCC removes the need to coordinate reads at all.

Watch versions & snapshots in action

Here are two concurrent transactions on one bank-account row. T1 is a long-running reader; T2 updates the balance. Step through and watch T2 create a new version while T1 keeps reading the old one — then flip the isolation level on the final step to see how it changes what T1 sees.

MVCC snapshot timeline

T1 isolation affects the final read ↓

version chain for row "balance"

● T1 — reader

● T2 — writer

Those xmin / xmax tags are real: in PostgreSQL every row stores the id of the transaction that created it (xmin) and the one that deleted or replaced it (xmax). A version is visible to a transaction when its xmin is committed-and-in-snapshot and its xmax is not. That single visibility rule is the whole engine of MVCC.

The four anomalies isolation prevents

Isolation levels exist to rule out specific anomalies — surprising results that happen when transactions interleave. Here are the four the SQL standard names, weakest to strongest:

reads garbage

Dirty read

You read another transaction's uncommitted change. If it rolls back, you acted on data that never truly existed.

value shifts

Non-repeatable read

You read a row twice in one transaction and get two different values, because someone updated and committed it in between.

rows appear

Phantom read

You run the same WHERE query twice and a new matching row shows up the second time — inserted by another transaction.

logic breaks

Write skew

Two transactions each read an overlapping set, then write based on it. Both commit — producing a state no one-at-a-time order could.

The isolation-level matrix

Every level is defined by which anomalies it forbids. Higher levels are safer but allow less concurrency (and may force retries). Click a level to see what it guarantees, or an anomaly to see its scenario and which levels stop it.

SQL isolation levels✓ prevented · ✗ possible

✓ anomaly prevented ✗ anomaly possible ✓* prevented by PostgreSQL's stricter snapshot (standard allows it)

The default is rarely the strongest. PostgreSQL and Oracle default to Read Committed — fast, but it allows non-repeatable and phantom reads. If your transaction reads a value, makes a decision, and writes based on it, that gap is a real bug waiting to happen. Know your engine's default, and raise it deliberately when correctness demands.

A classic bug: the lost update

Even with snapshots, two transactions reading-then-writing the same row can clobber each other. Watch it unfold — and see how the database stops it.

💬 #two-people-one-balance

This is why MVCC code retries. Under Repeatable Read or Serializable, the database detects the conflict and aborts one transaction rather than silently losing data. Your application must be ready to catch a serialization failure and retry the transaction — a small price for correctness. (The alternative: SELECT … FOR UPDATE to lock the row explicitly.)

Nothing is free: the cost of versions

Keeping old versions has a downside — they pile up. Every update and delete leaves a dead version behind that no live transaction can see. Left unchecked, the table bloats and scans slow down.

Dead tuples accumulate

Each update writes a new version and leaves the old one as garbage once no snapshot needs it.

bloat

VACUUM reclaims them

A background process (PostgreSQL's VACUUM) scans for versions older than every live snapshot and frees their space.

cleanup

Long transactions are the enemy

One transaction left open for hours pins every version created since it began — vacuum can't reclaim them, and the table bloats.

gotcha

The real-world lesson: never leave a transaction open longer than it needs to be. An idle-in-transaction connection isn't just holding a snapshot — it's blocking cleanup of every row version created since, slowly degrading the whole database. "Keep transactions short" is MVCC's golden rule.

Check yourself

Concept

Under MVCC, why does a reader never block a writer?

Correct. The old version stays valid for snapshots that predate the write. Reader and writer touch different versions, so neither waits.

Scenario

A report reads a row, runs for 30s, reads it again, and the two values differ. The level is Read Committed. What happened, and what fixes it?

Right. Read Committed re-snapshots each statement, so a committed update between the two reads is visible. Repeatable Read freezes one snapshot for the whole transaction.

Debugging

Your PostgreSQL table is 10× its expected size and scans are slow, though row counts look normal. Likely cause?

Exactly. Dead tuples that no snapshot needs should be vacuumed, but an old open transaction pins them. Find and close the long transaction; let VACUUM reclaim the space.

Architecture

Two transfers each check a shared balance and withdraw; both pass the check and both commit, overdrawing the account. Which anomaly, and which level prevents it?

Correct. Each transaction's read was valid in isolation, but together they produce an impossible state. Only Serializable (or explicit locking) catches this conflict and aborts one.

Up next, the finale: the engine is fast, durable, safe under load, and now scales concurrency with versions. One question remains — how do you see it all working while it runs? Metrics, structured logs, and a live dashboard.