MVCC: Why Readers Never Block Writers

Multi-version concurrency control is the trick that lets modern databases run heavy reads and heavy writes on the same row at the same time without anyone blocking anyone. The core idea is small. Never overwrite a row in place. When a writer changes a row, the engine appends a new version, leaves the old one in place, and tags both with the transaction id that created them. A reader sees whichever version was committed before its own snapshot started, ignoring everything newer.

Walk through a concrete example. Transaction A starts at time 100 and is going to scan a million rows. Transaction B starts at time 101 and updates row 42. Without MVCC, one of them has to wait. Either A holds a read lock and B blocks, or B holds a write lock and A blocks. With MVCC, B writes a new version of row 42 tagged with txn 101 and commits. A keeps reading, and when it reaches row 42 it sees version 100 because that is the version visible at its snapshot time. Neither transaction ever waits on the other. That is what "readers don't block writers and writers don't block readers" actually means in implementation.

The two mainstream engines implement this differently. Postgres stores all versions in the heap itself. Each row, called a tuple, carries xmin (the transaction that created it) and xmax (the transaction that deleted or superseded it). A row is visible to your snapshot if xmin committed before your snapshot and xmax did not. Old versions accumulate in the table, which is why Postgres has VACUUM. Without VACUUM, you get table bloat, index bloat, and eventually a transaction id wraparound emergency where the database refuses new writes. MySQL InnoDB does the opposite. The current version sits in the table and old versions live in a separate undo log, chained back from the current row. A reader walks the undo chain backward until it finds a version visible to its snapshot. The purge thread eventually drops undo entries that no active transaction can still see.

Both shapes give you snapshot isolation: every transaction sees a consistent view of the database as of its start. Both pay the same two costs. Extra storage for the old versions, and a background cleaner that has to keep up with the write rate. On a write-heavy table with long-running read transactions, that cleaner can fall behind. The result is bloat or a stalled purge, and the symptom is read latency creeping up while disk usage climbs. The fix is almost always to shorten the long-running transactions, not to tune the cleaner.