Databases DB · 04 · 01

MVCC: why readers and writers never wait for each other

MVCC keeps every row''''s history so each transaction sees a stable snapshot without locking the rows other people are touching.

DB Junior ◷ 8 min

Level

FoundationsJuniorMiddleSenior

Already know this unit? Take a 1-minute quick check →

A five-minute analytics report and a high-volume checkout flow are running at the same time on the same database. Without special design, one would freeze the other. With MVCC, neither waits — and the trick costs nothing at read time.

What MVCC does in one sentence

MVCC keeps every row’s history so each transaction sees a stable picture of the database without having to lock the rows other people are touching.

Why care. Without it, a five-minute analytics query would freeze every order coming in, and your dashboard would either lie about totals or block the checkout button. By the end of this lesson you will know exactly why that never happens with MVCC — and where the one gap is that it leaves to you.

The library metaphor

Imagine a busy library where every book has many copies stamped with the date they were taken off the shelf. When a reader asks for “the catalog as of when I started reading”, the librarian hands them a labeled copy frozen at that moment, even while a librarian behind the counter is binding a new edition. The reader reads their copy in peace. The librarian adds a new edition. Neither has to wait for the other; they are looking at different stamped copies of the same shelf.

Later, a janitor sweeps through and discards copies nobody is reading anymore. That sweep is what Postgres calls VACUUM, and the janitor only throws out a copy when no reader is still holding its date-stamp.

Without MVCC	With MVCC
Reader locks the row; writer waits	Reader sees an old version; writer creates a new one
Writer locks the row; reader waits	Writer creates a new version; reader still sees the old
Analytics query blocks checkout	Analytics query reads frozen snapshot; checkout proceeds

Sven and Otto — the concrete scenario

An app server runs a monthly report (Sven). The database receives thousands of orders per second (Otto). Without MVCC, the report either locks the orders table (freezing checkout) or sees half-finished updates. With MVCC, Sven gets a snapshot frozen at the report start; new orders go to fresh versions Sven cannot see. Report finishes, checkout never stalls.

The lost-update gap MVCC does not close

Two browser tabs edit the same profile. Tab A loads, you type. Tab B loads, edits one field, saves. You save Tab A: Tab B’s edit silently disappeared. That is lost update. MVCC alone does not fix it — it still gives both tabs a valid snapshot, but does not serialize their writes. Ask the database with SELECT FOR UPDATE or a stricter isolation level (covered in lesson 03).

▸Why this works

MVCC was not invented by Postgres. The concept dates to 1978 (Reed’s work at MIT). Oracle shipped it in version 7 (1992); Postgres had a form of it from the very start, and it was one reason Postgres was chosen over competitors in multi-user workloads. MySQL InnoDB added MVCC in 2000.

Quiz

What does MVCC actually do?

Quiz

A long-running analytics query and a high-volume checkout flow are running at the same time. With MVCC, what happens?

Order the steps

Put the life of one row's update in order:

1 Transaction A starts; gets snapshot tagged with its transaction id
2 Transaction A reads the row at version v1 — the version stamped before A started
3 Transaction B updates the row, creating version v2 stamped with B's transaction id
4 Transaction B commits — v2 is now the latest visible version for new snapshots
5 Transaction A still reads v1 because that is what its snapshot allows
6 Eventually no snapshot needs v1; VACUUM marks v1's space reusable

The same lifecycle the exercise above asks you to order: a version is born on UPDATE, kept alive by an active snapshot, and only reclaimed by VACUUM afterwards — that delay is MVCC's storage cost.

Complete the analogy

Fill in the blank: MVCC is like a library where each reader gets a date-stamped _______ instead of the shelf book itself.

Time flows top to bottom. The reader keeps seeing v1 from its frozen snapshot while the writer commits v2 — neither transaction waits for the other.

Recall before you leave

01
In one sentence: why does a five-minute analytics query in Postgres not block checkout writes?
02
What is a lost update, and why does MVCC not prevent it?
03
What does VACUUM do and why is it needed after MVCC updates?

Recap

MVCC keeps every row as a chain of versions, each stamped with the transaction that wrote it. When a transaction starts, Postgres gives it a snapshot — a frozen view of which versions are visible. Readers see old versions; writers create new ones; neither ever blocks the other. The storage cost is dead versions accumulating until VACUUM reclaims them. MVCC does not prevent lost updates: two concurrent transactions can still overwrite each other’s work if the application does not use row-level locking or a stricter isolation level. Now when you see a long analytics query running alongside peak checkout traffic, you know neither is waiting — and if two tabs try to save the same profile at once, that is the gap MVCC leaves to you.

Practice

Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.

recallapplystretch0 of 6 done

Connected lessons

builds on

EXPLAIN and execution plans: what the planner decides and whyjunior

unlocks

deepens into

appears again in173

Something unclear?

Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.