MVCC

MVCC, short for multiversion concurrency control, is a technique by which a database keeps several versions of each row so that every transaction reads a consistent snapshot of the data while other transactions modify it. In database transactions, its core promise is that readers do not block writers and writers do not block readers.

Instead of overwriting a row in place and locking it against readers, an MVCC engine creates a new row version for each change and decides, for every reading transaction, which version is visible. PostgreSQL, Oracle, MySQL with InnoDB, SQL Server with snapshot isolation and many newer systems rely on MVCC to implement isolation levels efficiently.

How MVCC keeps multiple row versions

Each transaction receives a transaction identifier or timestamp when it starts or when it first writes. Every row version is stamped with the identifier of the transaction that created it and, once replaced or deleted, the identifier of the transaction that ended its validity.

When a transaction reads, it uses a snapshot: a record of which transactions had committed at a given moment. A row version is visible if its creator committed before the snapshot and its deleter, if any, had not. This visibility check runs for every row a query touches.

1. Transaction A reads a row and sees version 1.
2. Transaction B updates the row, creating version 2 and marking version 1 as replaced by B.
3. Transaction A reads again and still sees version 1, because B had not committed when A’s snapshot was taken.
4. After B commits, new transactions see version 2, and version 1 becomes garbage once no snapshot needs it.

Indexes complicate the picture. In PostgreSQL an index entry points to a specific row version, so an update may add new index entries even when indexed columns did not change, unless a heap-only tuple update keeps the new version on the same page. Index-only scans also need a visibility map to confirm that rows are visible without visiting the table.

Implementation approaches in database engines

Engines differ mainly in where old versions are stored and how they are cleaned up. This choice affects storage growth, update cost and maintenance work.

Old versions must eventually be removed. PostgreSQL relies on VACUUM, usually run by the autovacuum daemon, to reclaim space from dead tuples, while InnoDB runs a purge process that discards undo records no longer needed by any active read view.

Transaction identifiers are finite. PostgreSQL uses 32-bit transaction IDs, so it must periodically freeze old rows to prevent transaction ID wraparound, a maintenance task that can become urgent on very busy databases if vacuuming falls behind.

MVCC and isolation levels

MVCC provides the mechanism, and the isolation level decides how snapshots are used. The key difference is whether a transaction takes one snapshot for its whole duration or a fresh snapshot for each statement.

Snapshot isolation is not serializable. In write skew, two doctors each check that another doctor is on call and both go off duty, and each transaction is valid against its own snapshot. PostgreSQL addresses this with serializable snapshot isolation, which tracks read and write dependencies and aborts one transaction when a dangerous pattern appears.

MVCC compared with lock-based concurrency control

The traditional alternative is two-phase locking, where readers take shared locks and writers take exclusive locks. MVCC does not eliminate locks, since concurrent writers to the same row still conflict, but it removes most read-write blocking.

Because readers never wait, MVCC suits mixed workloads where long reports run alongside frequent short updates. The price is extra storage and background cleanup, and a rollback in an undo-based engine must restore the old version, which can take time for large transactions.

Operational pitfalls of MVCC

Most MVCC trouble comes from old snapshots that live too long. As long as any transaction may still need an old version, the engine cannot remove it, so one forgotten session can make the whole database grow.

• Long-running transactions hold back cleanup and cause table and index bloat or a growing undo history.
• Idle in transaction sessions, where an application opened a transaction and never committed, have the same effect.
• Update-heavy tables produce many dead versions and need tuned autovacuum or purge settings.
• Lost updates under read committed occur when two transactions read, modify and write the same value; use SELECT ... FOR UPDATE or atomic updates.
• Serialization failures at serializable level are expected, so applications must retry the transaction.

Monitoring the age of the oldest open transaction, dead row counts and undo history length catches most of these problems before they affect users.

Understanding it simply

MVCC works like a shared document that keeps earlier drafts while someone edits it. A reader who opened the document at ten o’clock keeps seeing the ten o’clock version, even while a colleague makes changes, so the reader never sees a half-finished edit.

When the colleague saves, new readers see the new version. Once nobody is reading the old draft, it is thrown away. Everyone can work at the same time without waiting for each other, at the cost of storing a few extra copies for a while.

Applied in practice

A logistics company runs an end-of-day inventory report that takes about 20 minutes on its PostgreSQL database, while warehouse scanners update stock levels hundreds of times per minute. Thanks to MVCC, the report runs at repeatable read and sees one consistent snapshot, and the scanners never wait for it.

Over several weeks the stock table grows to many times its expected size. Investigation shows a reporting tool that left a session idle in transaction for days, preventing autovacuum from removing dead row versions. The team sets an idle transaction timeout and adds monitoring of the oldest transaction age, and the table returns to a stable size after the next vacuum cycle.