MVCC: How Readers Avoid Blocking Writers
Keep the old version of a row alongside the new one, and a reader never has to wait for a writer.
Overview
The problem with locks
The simple way to keep transactions from interfering is locking: a reader takes a shared lock, a writer takes an exclusive one, and the two cannot be held at once.
It is correct, and on a busy system it is miserable. A report that scans a large table holds read locks for its whole run, so every write to that table waits. Meanwhile a long write transaction blocks every reader. The database spends its time queueing rather than working, and the symptom is an application that is fast until it is inexplicably not.
MVCC: How Readers Avoid Blocking Writers
Step through and watch what A sees. Under MVCC it never waits, and it never sees B's change - because A's snapshot predates it.
Worth knowing
VACUUM.MVCC: How Readers Avoid Blocking Writers
The idea that lets a long report run while the database keeps taking writes.
Versions instead of locks
Multi-version concurrency control removes the conflict by refusing to destroy anything. A write does not overwrite a row; it creates a *new version* of it, and the old version stays until nobody can still need it.
Every transaction is given a snapshot — effectively a rule saying which versions count as visible — fixed at the moment it starts, or at the moment each statement starts, depending on the [isolation level](isolation_levels.html).
A reader that began before a write simply continues to see the older version. Nothing waits.
What each version carries
Conceptually each row version records which transaction created it and which transaction deleted it:
id | balance | created_by | deleted_by
----+---------+------------+-----------
7 | 1000 | 100 | 142
7 | 850 | 142 | NULLTransaction 142 changed the balance. It did not edit the first line; it marked it deleted and appended the second. A transaction with a snapshot older than 142 is shown the first version, a newer one the second. Visibility becomes an arithmetic comparison of transaction ids rather than a queue.
What MVCC does not solve
This is the part that gets missed. MVCC eliminates reader/writer conflicts. It does not eliminate writer/writer conflicts.
Two transactions updating the same row still contend: the second must wait for the first to commit or roll back, because they would otherwise both produce a new version from the same old one and one update would vanish. That is the [lost update](isolation_levels.html) problem, and MVCC's answer to it is either a lock on that row or a serialisation failure at commit.
So the rule is: readers never wait, writers to the same row still do.
The cost: garbage
Old versions accumulate. Something has to decide when a version can no longer be seen by any live snapshot and reclaim the space.
In PostgreSQL that job is VACUUM, usually run by autovacuum. When it cannot keep up — typically because a very old transaction is still open and pinning every version created since — tables bloat: the row count is unchanged while the file grows, and every scan reads more pages for the same data.
The practical consequence is worth stating plainly: a transaction left open does damage even when it is doing nothing, because it holds back cleanup for the whole database. An idle-in-transaction connection is a bug, not a small inefficiency.
Different engines pay this differently. PostgreSQL keeps old versions in the table itself and vacuums them. MySQL's InnoDB keeps them in a separate undo log and purges it. Oracle uses undo segments, and the same open-transaction problem appears as ORA-01555 snapshot too old — the old version needed was already discarded.
What an open transaction actually costs
"Does damage even when it is doing nothing" is measurable. A snapshot cannot be released while any transaction that might still need it is open, so every row version superseded *after* that transaction began has to be kept.
Picture a table taking a steady 1,000 updates a second. Each update leaves a dead version behind. A reporting transaction that opens and then sits idle — a connection returned to a pool without a commit, a debugger stopped at a breakpoint — pins cleanup for as long as it stays open:
| idle for | dead versions pinned |
|---|---|
| 1 minute | 60,000 |
| 10 minutes | 600,000 |
| 1 hour | 3,600,000 |
The live row count never moved. But the table now holds millions of dead versions the vacuum cannot touch, every scan reads the pages they sit on, and the file keeps growing. One forgotten transaction, doing nothing, taxes every query against that table until it is closed — which is why "idle in transaction" is a state worth alerting on, not just avoiding.
Visibility is one comparison
The claim that visibility becomes arithmetic rather than a queue is worth making literal, because it is why reads never wait. A version is visible to a snapshot S exactly when created_by <= S AND (deleted_by IS NULL OR deleted_by > S). Take the two versions from earlier — v1 created by 100 and deleted by 142, v2 created by 142 and still live:
| snapshot | v1 (100–142) | v2 (142–) | sees |
|---|---|---|---|
| 120 | 100≤120 and 142>120 → yes | 142≤120 → no | v1, balance 1000 |
| 150 | 142>150 is false → no | 142≤150 and live → yes | v2, balance 850 |
No lock is consulted and nothing blocks; each reader compares transaction ids and picks its version. A snapshot of 120 keeps seeing 1000 no matter how many times 142 commits, because the numbers do not change once fixed. That is the entire mechanism by which a long report runs undisturbed beside a stream of writes. It is also why the visible version is found without a scan of the history: the engine keeps versions chained newest-first and walks until the comparison first succeeds, which for a recently-read row is usually the very first link.
Where it goes wrong
Long-running transactions. They pin versions across the entire database. Keep them short, and never leave one open across a user interaction.
Assuming no waiting at all. Writers to the same row still contend, and the error surfaces as a lock wait or a serialisation failure.
Treating table bloat as a disk problem. It is a symptom of vacuum not keeping up, and adding disk hides it rather than fixing it.
Counting rows to check for bloat. The row count is right; the file size is the thing that grew.
Check yourself
0 of …Answer without scrolling back up.
What does a write do under MVCC?
Which conflict does MVCC NOT remove?
Why does an idle-in-transaction connection cause table bloat?
MVCC: How Readers Avoid Blocking Writers
The simple way to keep transactions from interfering is locking: a reader takes a shared lock, a writer takes an exclusive one, and the two cannot be held at once.