MVCC without the jargon

Somebody told you that readers never block writers and writers never block readers under MVCC, and it sounded like something for nothing — two sides of a system that used to fight over the same row, now apparently not fighting at all. It is not free. The database is not skipping the conflict; it is paying for a way around it, and the price is that old versions of a row have to be kept around for as long as anyone might still be looking at them. That price does not show up where you would first look for it, on the write itself — it shows up later, as extra work for whoever cleans up. What follows is where that price comes from and who actually pays it.

Two versions of the same row#

A write does not overwrite a row: it does not destroy the previous version, only adds a new one. In the model this article uses, the old version stays exactly where it was — though some databases move it into a separate undo area instead. Each version carries the identity of the transaction that created it, and — once something has superseded or deleted it — the identity of the transaction that ended it too. Nothing about this requires a reader to coordinate with a writer: a reader does not take a lock and does not wait for one to be released. It walks the versions of a row and picks the one it is allowed to see, entirely on its own.

This is what makes two transactions able to disagree about a row and both be correct. One began before a write committed and still sees the version that was current then; another began after and sees the new one. Neither is stale in any sense that matters to it, because neither ever agreed to see the same instant as the other. A lock-based system avoids that disagreement by making the second transaction wait until the first is done deciding what “current” means. MVCC avoids the wait by giving up on there being one shared answer to what a row currently is.

That is the half of the story people arrive with, and stated alone it is a half-truth. The full claim has two parts, and the second is the one that gets dropped: readers do not block writers, and writers do not block readers — but two writers to the same row still serialise. One has to wait for the other, or be told to retry, because there is no version of “both changes happened first” that a single row can hold. MVCC removes exactly one conflict, the one between a reader and a writer. It does nothing to the conflict between two writers, because there was never a version-visibility trick that could make two contradictory writes both be true.

What a snapshot actually is#

The word suggests a copy of the whole database, taken at an instant, and that is not what it is — copying everything on every transaction would cost far more than the problem is worth, and most of a database is irrelevant to any one query anyway. A snapshot is a record of which transactions had already committed at the moment the current one began. Every version a reader encounters is judged against that record: was it created by a transaction that had committed by then, and — if it was later superseded — was the transaction that superseded it still uncommitted at that same moment? A version that passes both tests is the one the transaction sees, regardless of what has happened to the row since. Nothing about that record depends on the size of the table or the row being read; it is a fact about other transactions, not about the data itself, which is exactly why taking it can be cheap.

Two consequences follow from that, and both are worth stating plainly rather than leaving implied. The first is what a snapshot buys the transaction that holds it: a view that does not shift under it, no matter how long the transaction takes to finish, which is exactly what lets a slow report walk a busy table and still come out internally consistent. The second is what a snapshot costs everyone else: every version that any still-live snapshot might legitimately need has to be kept somewhere, unremoved, until that snapshot is gone. The cost of MVCC is therefore not paid at write time — a write is cheap precisely because it does not have to negotiate with a reader. It is paid afterward, by whatever process is responsible for cleaning up versions nobody can see anymore, and the length of the oldest still-open transaction is what decides how much that process has waiting for it.

A snapshot held open too long#

The shape to recognise is a long-running read slowing down the write path. A reporting query, a nightly export, or a session that opened a transaction and never closed it all do the same thing: they hold a snapshot open. From the moment that snapshot was taken, every version created anywhere in the database has to be kept in case that one snapshot still needs it — not just in the tables the report is reading, but in tables it never touches at all, because nothing tracks which tables a given snapshot cares about in advance. Writes elsewhere in the system get slower, and space used for old versions grows, and the cause of both is a read that is not even running a write.

The tell worth naming is specific, because it is what makes this diagnosable instead of mysterious: the slowdown tracks a report’s schedule rather than write volume, and it will not reproduce under load testing, because a load test rarely holds a single transaction open across the whole run the way a forgotten session or a long export does. Look for what has a transaction open, not for what is writing too much — and look outside the table that appears to be suffering, because the session responsible is often working against something else entirely and simply happens to be sitting there, connected, with nothing committed.

No release will patch this out. The same mechanism that lets the report run without blocking a single writer is the one obliging the database to keep every version that report’s snapshot might still need — the cost and the benefit are the same fact seen from two sides. The fix is bounding how long a transaction is allowed to stay open, not concluding that MVCC was the wrong choice.

A different kind of read cost, unrelated to what a transaction can see, is covered in Why your index is not being used — about the point where the planner stops trusting an index and reads the whole table instead. And this question falls closest under the persistence path, the roadmap that starts from what a row physically is — though that roadmap runs past this question rather than through it.