MVCC: Readers Don't Block Writers

Two people open the same customer record at 2:07 PM. One is running a five-minute report that reads ten thousand rows. The other just wants to fix one phone number. In an older database the report holds a read lock on every row it touches, so the phone-number change waits — sometimes for the whole five minutes. Flip the roles and it's worse: one slow write can freeze every reader stacked up behind it.

That waiting is the tax of using locks to keep concurrent transactions off each other's toes. And it's a tax almost no modern database pays anymore. Postgres, MySQL's InnoDB, Oracle, SQL Server in its snapshot mode, CockroachDB — nearly every database built in the last twenty years runs on the same idea instead: Multi-Version Concurrency Control, or MVCC.

Here's the whole idea in one image. Think of a printed newspaper. Once your morning copy comes off the press, the words in your hands don't change all day — even though the newsroom is already writing tomorrow's edition. MVCC gives every transaction its own printed copy of the data, frozen at the moment it started. A writer doesn't scribble over your copy; it prints a new edition beside it. You read yours, they print theirs, and nobody waits.

The slogan is readers don't block writers, and writers don't block readers — and by the end of this lesson you'll know exactly why it's true, down to the two little transaction-id stamps on every row that make the whole thing work. On a real Postgres, we'll watch a reader and a writer hit the same row at the same instant: the reader finishes in 5.8 ms without waiting a beat.

In this lesson: why locks make readers and writers fight; how versioning ends the fight; what a snapshot is and the single visibility rule that decides what each transaction sees; why the same query returns different answers under Read Committed vs Repeatable Read; and the bill the versions leave behind — dead rows, bloat, and VACUUM.

Scope: this is the mechanism. The full menu of isolation levels and the anomalies each one lets slip through — dirty reads, phantoms, write skew, serializable — gets its own lesson in Isolation Levels & Their Anomalies. Choosing to lock-first vs check-last is Pessimistic vs Optimistic Locking. Keeping replicas in sync across machines is a different problem entirely — that's The Consistency Spectrum and CAP, Properly. Here we stay inside one database engine and watch how it lets many transactions share the same rows without anyone standing in line.

To appreciate the trick, you have to feel the problem it kills.

The simplest way to stop two transactions from corrupting each other is a lock. Before you read a row, grab a shared lock; before you write it, grab an exclusive lock. Shared locks tolerate each other — many readers at once, fine — but a shared lock and an exclusive lock refuse to share a row. So a reader blocks a writer, and a writer blocks everyone. That's two-phase locking, and it is perfectly correct. It just forces reads and writes into single file whenever they land on the same data.

For a system where reads and writes overlap all day, that's rough. A reporting query scanning a table can stall every update to it for as long as it runs. A nightly job updating a million rows can make the live dashboard time out. The database burns its time not on work but on waiting for permission.

So somebody asked the question that cracks the whole thing open: the reader wants the value as it was, the writer wants to make the value as it will be — why are they fighting over one shared copy of the row at all? What if they didn't have to share a copy?

MVCC stops treating a row as a single cell you overwrite and starts treating it as a stack of versions over time. When you UPDATE a row, the database does not change the existing bytes. It writes a whole new copy of the row with the new values and leaves the old copy sitting exactly where it was. Now two physical rows represent one logical row — an old edition and a new one. The reader who started earlier keeps reading the old edition; the writer prints the new one beside it. They never touch the same bytes, so there is nothing to fight over.

How does the engine keep the editions straight? Every version carries two transaction-id stamps in its header. Postgres calls them xmin and xmax:

• xmin — the id of the transaction that created this version.
• xmax — the id of the transaction that retired it (updated or deleted it over), or 0 if this version is still live and current.

An UPDATE is really "retire the old, create the new," done atomically: the old version gets its xmax stamped with your transaction id, and a fresh version is written with xmin = your id and xmax = 0. A pointer on the old version (Postgres calls it ctid) points at the new one, so the engine can walk forward from an old version to the newer editions. That linked list of versions is the version chain.

A DELETE is even lazier: nothing is erased. The current version just gets its xmax stamped — "gone as of this transaction" — and the bytes stay on the page. If that rings a bell, it's the same move as the tombstone from LSM Trees & SSTables: a delete that adds a marker instead of removing data. MVCC is that idea living inside an ordinary heap table.

This isn't a metaphor — you can read the raw versions straight off the page. On a live Postgres, transaction 734 inserted id=1 with balance 500, then transaction 735 updated it to 450. Peek at the actual heap page with pageinspect and both physical versions are still there:

One logical row (id=1), two physical tuples. The old one's xmax=735 says "the update retired me," and its ctid=(0,3) is the chain link pointing at the new edition. Nothing was overwritten — the update just appended. Which raises the obvious question the rest of the lesson answers: with two editions of the row on the page, which one does a given transaction actually get?

The answer is that when a transaction needs a consistent view, it takes a snapshot — a small record of "which transactions had committed at this exact instant." It isn't a copy of the data; it's a copy of who counts. It's the timestamp on your printed newspaper: the edition line that says which stories had made it to press when your copy was pulled.

In Postgres a snapshot is three things captured in one instant:

• xmin — the oldest transaction still running. Everything older has already finished, so its fate (committed or aborted) is settled.
• xmax — the next transaction id that hasn't been handed out yet. A ceiling: anything at or above it started after you, so it can't be visible to you.
• xip[] — the list of transactions that were still in flight (uncommitted) at that instant.

Postgres even prints a snapshot compactly as xmin:xmax:xip. So 3695:3697:3695 reads as: the oldest running transaction is 3695, the next unassigned id is 3697, and transaction 3695 was mid-flight when I looked. Your transaction now carries that snapshot around like an as-of timestamp — and every row it meets is judged against it by one rule.

Each version you encounter is checked against your snapshot with a single question, asked of its two stamps:

A version is visible to you if the transaction that created it (xmin) had already committed as of your snapshot — and the transaction that retired it (xmax) had not.

That's two halves:

1. Is it born yet, for me? The creator xmin has to be a transaction that committed before your snapshot — so xmin is below your snapshot's xmax and isn't in your in-flight list. A row inserted by a transaction that's still running, or that started after you, is invisible — as if it isn't there.
2. Is it dead yet, for me? The retirer xmax must not be a transaction that had already committed before your snapshot. If xmax is 0 (never retired), or the retiring transaction is still in flight, started after you, or aborted — the version is still alive for you.

The engine walks the version chain and keeps the one edition that passes both tests. That's the entire magic. Two transactions reading the same row at the same moment can honestly see two different values, because each keeps the edition that was the committed truth as of its own snapshot — no locks, no waiting, just a couple of integer comparisons per row.

Here's a concrete case. Your snapshot is 3695:3697 with 3695 still in flight. You meet three rows:

Three rows, three verdicts, each decided in a few comparisons while the query runs. A writer can be busy stamping brand-new versions the entire time — it never touches what your snapshot already decided. That's not a slogan; it's measurable. On a live Postgres, while one transaction held an open read snapshot on a row, another transaction's UPDATE of that same row finished in 5.833 ms — it never waited. That is why readers don't block writers.

Now the elegant part. The visibility rule never changes. The only thing an isolation level changes is how often you take a fresh snapshot — how often you buy a new newspaper.

• Read Committed (Postgres' default) takes a new snapshot at the start of every statement. Each query sees the latest data committed the instant it began. It's like grabbing a freshly printed edition before reading each article — always current, never stable.
• Repeatable Read takes one snapshot at the transaction's first statement and freezes it for the whole transaction. It's buying one paper in the morning and reading it all day. Every read sees the same consistent moment, no matter who commits what around you. (This is exactly what DDIA calls snapshot isolation.)

Same machinery, two guarantees, decided purely by the scope of the snapshot. Watch it on a real Postgres. A row starts at balance 450. Session B reads it while Session A changes it:

Under Repeatable Read the database looked frozen in time from B's seat — 450, then 450 again — even as it truly changed to 999 for everyone else. Under Read Committed, the very same pair of SELECTs returned 450 and then 777, inside one transaction. Neither B ever waited on a lock to get its consistent view.

One caveat worth carrying to interviews: "Repeatable Read" means different things in different databases, so name what it does, not the label. Postgres's Repeatable Read is genuinely snapshot isolation — and it's actually stronger than the SQL standard's Repeatable Read, because it also blocks phantom reads, which the standard doesn't require. The full anomaly-by-anomaly breakdown is the job of Isolation Levels & Their Anomalies; here, the thing to lock in is that the level is nothing more than the snapshot's scope.

Reading the rule is one thing; running the schedule yourself is another. Below you drive two concurrent transactions against one row. You pick the interleaving — when each one reads, writes, and commits — and watch the version chain grow, each transaction's snapshot freeze, and the visibility rule decide what every SELECT returns. Flip the isolation level from Read Committed to Repeatable Read and re-run the same read to feel the snapshot scope flip the answer.

Try this first: let T2 update the row and commit while T1 is still open, then have T1 read again. Under Repeatable Read, T1 still sees the old value; under Read Committed, it sees T2's new one. Either way, notice the thing that makes MVCC MVCC — T1's read never waits for T2's write.

Nothing is free, and you've probably already spotted the catch. If a write never overwrites and a delete never erases, old versions are piling up on every page. Where do they go?

A version that no active snapshot could ever need again is a dead tuple — the old copy after an update, or the tombstoned copy after a delete, once every transaction old enough to see it has finished. They aren't cleaned up on the spot; they sit on the page taking space until a background process reclaims them. In Postgres that process is VACUUM (usually autovacuum, running quietly in the background).

The cost is real and easy to measure. Load a table with 100,000 rows and it's 3,544 kB. Update every row five times — same 100,000 live rows, just newer editions — and the table balloons to 21 MB, carrying 499,905 dead tuples. That extra ~18 MB is pure history. Run VACUUM and the dead count drops back to 0 — but here's the nuance that surprises people: the file stays 21 MB. VACUUM reclaims the dead space for reuse inside the table; it doesn't hand it back to the operating system. Shrinking the file needs a VACUUM FULL (or pg_repack), which rewrites the table and takes a heavy lock.

And the table isn't the only thing paying. Every index on it points at those versions too, so the indexes bloat right along with the heap — which is the other half of the write tax from Indexes Deep-Dive. An index isn't just slower to maintain because it's another structure to update; it's also carrying entries for rows that no longer exist.

But VACUUM can't remove any old version it likes — one might still be visible to a long-running transaction. So it obeys the xmin horizon: the id of the oldest transaction still alive anywhere in the system. Nothing newer than that horizon can be reclaimed, because someone might still need to read it. And that leads straight to the failure mode every Postgres operator learns the hard way — one long-running transaction pins the horizon and blocks cleanup for the whole database.

You can watch it happen. Hold a single Repeatable Read transaction open, then churn 300,000 dead versions behind it. VACUUM VERBOSE reports, word for word:

Three hundred thousand dead rows it is forbidden to touch, because one idle in transaction session — visible right there in pg_stat_activity — is holding an old snapshot. The instant that session commits, the very same VACUUM reports 300000 removed. This is the number-one cause of runaway bloat in the wild: not heavy write traffic, but a forgotten open transaction.

Be precise about why, though, because the sloppy version of this rule will mislead you. It isn't that any open transaction blocks cleanup — it's that an old snapshot does. A transaction that opens after the dead versions were created never needed them, so it doesn't hold them hostage; VACUUM clears them happily. What pins your garbage is a snapshot older than the garbage itself. That's why the dangerous query isn't the busy one — it's the slow one, and the truly dangerous session is the one that opened a transaction and then went to lunch.

There's a deeper reason VACUUM isn't optional. Transaction ids are only 32 bits — about two billion of them — and visibility is decided by comparing ids. If the counter ever wraps around, ancient rows could suddenly look like they're from the future and vanish. Postgres defends by freezing old rows during VACUUM so they stop depending on the counter; if it can't keep up (often because something pinned the horizon), it will refuse new writes to save itself. It has happened to real teams — Mailchimp's Mandrill took a roughly 40-hour outage in 2016 when autovacuum fell behind and wraparound protection slammed the door. The takeaway isn't the runbook (that's an operations topic) — it's that MVCC's cheap, lock-free reads are financed by a cleanup process you have to keep fed.

MVCC is close to magic for the read/write clash. It's only honest to mark exactly where the magic stops.

Writers still block writers. "Readers don't block writers" is not "nobody ever blocks." If two transactions update the same row, the second waits for the first to commit or abort — there is still a lock on that row's latest version. The rule is first writer wins. Remember the reader that finished in 5.8 ms? On the same database, when a second transaction tried to update a row another had already locked, its UPDATE blocked for 2,114 ms — 360× longer — until the first one committed. Under Read Committed the loser then proceeds against the winner's new value; under Repeatable Read it gets ERROR: could not serialize access due to concurrent update and has to retry. MVCC removes read/write contention, not write/write contention.

Snapshot isolation isn't serializable. Because each transaction reads its own frozen snapshot, two of them can each read some rows, each change different rows based on what they saw, and commit a combination no serial order could ever produce — the write-skew anomaly. It's the famous hole in snapshot isolation, and closing it (Serializable, via SSI in Postgres) is a whole topic — the anomaly menu and the fix live in Isolation Levels & Their Anomalies. Just know the hole is there.

Someone always stores the history. Postgres keeps every version right in the table (append-only), which makes writes cheap but leaves bloat for VACUUM to chase. MySQL's InnoDB and Oracle make the opposite bet: the table holds only the current row, and each row carries a roll-pointer to a chain of before-images kept in a separate undo log; old versions are rebuilt on demand by walking that chain backward. The table stays compact — but old reads do more work, and the undo log has its own dead-version backlog, the history list length, cleaned by its own background purge thread. And here's the punchline: InnoDB has the exact same failure mode. A long transaction (the classic culprit is a big mysqldump) stalls purge, the history list balloons, and the undo tablespace bloats until the disk fills.

So the deepest truth of the lesson isn't "Postgres bloats" — it's this: the old versions have to live somewhere until every reader that might need them is gone, and one long reader keeps them all alive. MVCC didn't delete the reader/writer conflict. It moved the cost — from wall-clock waiting to storage plus background cleanup. That's usually a fantastic trade. It's never a free one.

You don't have to take the visibility rule on faith — you can watch xmin and xmax with your own eyes in about a minute, because Postgres exposes them as hidden system columns on every table. txid_current() tells you your own transaction id. Everything below is copied from a real run.

Predict before you read each result.

Predict: after the update, what is xmin on the row you can see? It's 735, the updating transaction — you're looking at the new edition, and the old one (xmin 734) is now a dead tuple waiting for VACUUM. Want to see the dead one too? CREATE EXTENSION pageinspect; then SELECT t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('account', 0)); — both physical versions are right there, the old one's t_ctid pointing at the new. The whole mechanism, in a table you made sixty seconds ago.

• "MVCC means no locks." It means reads take no locks. Two writers on the same row still serialize — first writer wins; the other waits (measured: 2,114 ms) or, under Repeatable Read, aborts and retries.
• "An UPDATE changes the row." Not in MVCC. It writes a new version and stamps the old one's xmax. The old bytes linger until VACUUM.
• "Repeatable Read shows data committed during my transaction." No — it froze its snapshot at your first statement. You won't see later commits until you start a new transaction. Read Committed is the one that re-snapshots per statement.
• "Read Committed gives me a stable view for my whole transaction." Also no — the same SELECT can return different rows twice in one Read Committed transaction (we watched 450 become 777). If you need stability, that's what Repeatable Read is for.
• "Old versions get cleaned up right away." No — they're dead tuples until VACUUM, and VACUUM can't touch anything newer than the oldest live transaction. One long transaction can block cleanup for the entire database.
• "A DELETE frees the space." No — it stamps xmax and leaves the bytes. Space returns only when VACUUM reclaims the dead version — and returns to the OS only after a VACUUM FULL.

• The core move: never overwrite. A write creates a new version; readers keep reading the version committed as of their snapshot. That's how readers don't block writers (measured: a reader finished in 5.8 ms while a writer changed the same row).
• Two stamps run the show: xmin (who created this version) and xmax (who retired it, or 0 if live). UPDATE = stamp the old xmax + append a new version; DELETE = stamp xmax only.
• A snapshot is "who had committed at this instant" (xmin / xmax / in-flight list) — your copy of the newspaper. The visibility rule keeps the one version whose creator committed before your snapshot and whose retirer didn't.
• Isolation level = snapshot scope. Read Committed re-snapshots every statement (fresh, not stable); Repeatable Read snapshots once per transaction (stable, but frozen). Name what a level does, not its label.
• The bill: dead tuples, bloat (3,544 kB → 21 MB in our run), and VACUUM — gated by the xmin horizon, so a long-running transaction is the classic cause of runaway bloat and even wraparound trouble.
• The limits: writers still block writers (first-writer-wins, 2,114 ms in our run), and snapshot isolation still allows write skew. MVCC moved the cost from waiting to storage; it didn't abolish it.

Next — How a Query Executes: now that you know how rows are stored, versioned, and made visible, we follow a single SELECT through the planner and executor, and learn to read EXPLAIN like the database narrating its own plan.