Understanding MVCC: Multi-Version Concurrency Control

1. What MVCC is and what problem it solves

MVCC, Multi-Version Concurrency Control, is a technique that lets a database allow several transactions concurrent access to the same data without reads waiting on writes or vice versa. Instead of locking a row on read, as classic lock-based concurrency control would, the database keeps several versions of each row available at once. A reading transaction sees the version that was valid at the time it started, regardless of what other transactions are writing in parallel.

The actual problem MVCC solves is the classic tradeoff between consistency and throughput: without versioning, a database would either need read locks that block writers, or reads would risk seeing inconsistent intermediate states while another transaction is mid-change. MVCC resolves this conflict structurally: a reader always gets a consistent, completed view of the data, without a writer ever having to wait for it and without it waiting for a writer.

Nearly all modern relational databases implement some form of MVCC. PostgreSQL, MySQL/InnoDB, Oracle, and SQL Server with its snapshot isolation option all share the same core idea but differ substantially in implementation detail. Those differences have direct practical consequences, from the need for regular maintenance jobs to subtle behavioral differences under heavy write load.

2. Row versioning: the PostgreSQL approach

PostgreSQL implements MVCC through physical duplication of the row on every change. An UPDATE does not modify in place, it creates a completely new physical row version with its own visibility metadata, the system columns xmin and xmax. xmin holds the transaction id that created this version, xmax the transaction id that invalidated it, if any. The old version stays physically in the table as a so-called dead row until a separate process removes it.

This architecture means a PostgreSQL table can physically occupy more disk space than the currently visible data would require after many UPDATE operations, because old row versions continue to exist until they are cleaned up. The upside of this approach: every transaction can, based on the xmin/xmax values and its own snapshot, determine exactly which version of a row is visible to it, without ever requesting a lock. That makes reads in PostgreSQL entirely lock-free with respect to concurrent writes.

-- PostgreSQL: system columns reveal MVCC row versions directly
SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 42;

-- Example output after two UPDATEs on the same row:
--  xmin | xmax | ctid   | id | balance
-- ------+------+--------+----+--------
--  1042 |    0 | (0,3)  | 42 | 850.00   -- current, visible version (xmax = 0 means not deleted)

-- The two older physical versions still exist on disk until VACUUM removes them,
-- they are simply no longer visible to any active transaction's snapshot

-- Inspect dead row ratio to judge how urgently a table needs vacuuming
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1), 3) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC
LIMIT 5;
-- A high dead_ratio on a hot table signals autovacuum is falling behind

3. Row versioning: the InnoDB approach

InnoDB, the default storage engine of MySQL, takes a different route to implement MVCC. Instead of keeping multiple physical copies of a row in the main table, InnoDB modifies the row in place and writes the previous version into a separate undo log segment. Every row internally carries a roll pointer referencing the corresponding undo log entry, and that entry can in turn point to an even older version, forming a chained list of historical versions.

For a reading transaction that needs to see an older version of a row because its snapshot predates an intervening change, InnoDB reconstructs the needed version at runtime by taking the current row and applying undo log entries backward until the matching version is reached. This approach keeps the main table more compact than PostgreSQL's approach, but shifts the cost into undo log management: long-running transactions that hold old snapshots open prevent InnoDB from releasing the corresponding undo log entries, which grows the so-called history list.

-- InnoDB: history list length grows with long-running transactions
-- Check current undo log pressure
SHOW ENGINE INNODB STATUS\G
-- Look for "History list length" in the TRANSACTIONS section

-- A long-running read transaction forces InnoDB to keep old undo records around
-- so its snapshot can still be reconstructed on demand
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT balance FROM accounts WHERE id = 42;  -- reads the snapshot as of transaction start
-- ... transaction stays open for a long time while other sessions keep updating accounts ...
COMMIT;  -- only now can InnoDB purge the undo records this transaction depended on

-- Identify the oldest active transaction, the usual root cause of a growing history list
SELECT trx_id, trx_started, trx_isolation_level, trx_rows_locked
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 5;
-- The oldest trx_started value is the one preventing purge from making progress

4. Snapshot isolation and read consistency

The central concept for understanding MVCC is the snapshot: at the start of a transaction, or depending on isolation level at every individual statement, the database records which transactions have already committed and which are still open at that point. Based on that information it can decide, for every row version, whether it is visible to the current transaction, without ever needing a lock on the row being read.

Under READ COMMITTED, the database captures a new snapshot on every statement, so a transaction can see different data states over its course, depending on what committed in the meantime. Under REPEATABLE READ, in both PostgreSQL and InnoDB, the snapshot is captured once at the start of the transaction and stays stable for the entire transaction duration, so all read operations within that transaction consistently see the same data state. This consistency is a direct result of MVCC and would require substantial locking overhead without versioning.

-- READ COMMITTED: a new snapshot is taken on every statement
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 42;  -- sees committed state at this moment
-- ... another session commits an UPDATE on account 42 here ...
SELECT balance FROM accounts WHERE id = 42;  -- may now return a different value
COMMIT;

-- REPEATABLE READ: one snapshot for the entire transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 42;  -- snapshot taken here
-- ... another session commits an UPDATE on account 42 here ...
SELECT balance FROM accounts WHERE id = 42;  -- still returns the original value
COMMIT;

5. How MVCC decouples readers from writers

The practical core benefit of MVCC shows most clearly in direct comparison with lock-based systems: in a purely lock-based model, a SELECT that wants a consistent view must either place a read lock on the affected rows or wait for an existing write lock to release. Under MVCC, both disappear: the reader gets its version of the data from the snapshot, regardless of whether a writer is currently changing the same row.

This decoupling is why long-running analytical queries in an MVCC system do not block write operations, even when they read millions of rows over several seconds. A reporting job and a concurrently running order process do not compete for the same lock under MVCC, because the reporting job sees its own, internally consistent snapshot while the order process writes new versions unhindered. The price for this is that old versions must be retained as long as any active transaction potentially still needs them, which leads directly to the topics of vacuum and purge.

6. Vacuum in PostgreSQL: cleaning up old versions

Because PostgreSQL physically leaves old row versions in the table, it needs a dedicated process to remove those dead rows once no active transaction depends on them anymore. This process is called vacuum, and the autovacuum daemon runs it automatically in the background by default whenever the share of dead rows in a table exceeds a configurable threshold. Vacuum marks the occupied space as reusable for future INSERT and UPDATE operations, but usually does not return it to the operating system immediately.

Without functioning vacuum, a table in PostgreSQL keeps growing continuously, a phenomenon known as table bloat, with direct consequences for sequential scan performance and index size. It becomes especially critical around transaction id wraparound: PostgreSQL uses a limited number of transaction ids, and if vacuum cannot run for a long time, for example because a very old transaction stays open and blocks the freeze process, the database can in the extreme case switch into a read-only emergency mode to prevent data loss from id wraparound. This tight coupling between MVCC and vacuum makes clear that maintenance in PostgreSQL is not an optional detail but an integral part of the consistency model.

-- Manually trigger vacuum on a specific table, with verbose progress output
VACUUM (VERBOSE, ANALYZE) accounts;

-- Check how close a database is to transaction id wraparound
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
-- A rapidly growing xid_age with no corresponding freeze activity is a warning sign

7. Purge in InnoDB: undo logs and the history list

InnoDB handles cleanup of old versions through a separate purge thread that removes undo log entries once no active transaction still needs the associated old row version. Unlike PostgreSQL, where dead rows remain in the main table, cleanup in InnoDB primarily concerns the undo tablespace, but it can also mean that rows marked as deleted are only physically removed with a delay.

The practical consequence of MVCC in InnoDB is the previously mentioned history list length: the longer a transaction stays open while other sessions repeatedly change the same rows, the more undo log entries InnoDB must retain so it can still reconstruct that long transaction's snapshot. A strongly growing history list leads to noticeably declining write performance and increasing storage consumption in the undo tablespace, which is why long-running transactions in InnoDB should be avoided just as much as in PostgreSQL, albeit for a slightly different technical reason.

8. Anomalies despite MVCC

MVCC solves many, but not all, concurrency problems automatically. Write skew is an anomaly that can occur even under REPEATABLE READ snapshot isolation: two transactions each read the same consistent snapshot, make independent decisions based on it, and both write successfully, even though the result violates a business rule that both transactions together should have upheld. The classic example: two doctors independently check whether a third doctor is still on duty before signing off themselves, both see the same snapshot with two doctors remaining, both sign off, and in the end nobody is on duty.

Only true SERIALIZABLE isolation, implemented in PostgreSQL as Serializable Snapshot Isolation, reliably detects such conflicts and aborts one of the involved transactions with a serialization failure error. This shows: MVCC alone guarantees snapshot consistency but not automatically full serializability. Anyone who needs true serializability must explicitly request it via the isolation level and handle serialization failures with a retry in application code.

Another limit of MVCC shows up with lost updates under READ COMMITTED: two transactions read the same value, independently compute a new value from it, and write it back, with the second write silently overwriting the first without the database reporting any conflict. The classic fix is explicit locking with SELECT ... FOR UPDATE at the critical point, which forces the second transaction to wait for the first instead of overwriting a stale snapshot unchecked. This targeted combination of MVCC for reads and explicit locks for critical writes is, in practice, the usual middle ground between full serializability and maximum throughput.

-- Explicit locking closes the lost-update gap that plain MVCC reads leave open
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- blocks concurrent writers
-- application computes new_balance based on the locked value
UPDATE accounts SET balance = :new_balance WHERE id = 42;
COMMIT;
-- A second concurrent transaction now waits for this lock instead of
-- silently overwriting the first transaction's update

9. MVCC implementations compared

The following table contrasts the two most important MVCC implementations and shows the practical consequences of their architectural differences.

Despite these implementation differences, both systems follow the same core principle of MVCC: readers see a consistent version of the data without blocking writers, and the cost is shifted into a separate, downstream cleanup process.

10. Summary

MVCC is the architectural foundation that lets modern relational databases achieve high concurrency without readers and writers blocking each other. Instead of using locks, the database keeps several versions of each row and shows every transaction exactly the version matching its snapshot. PostgreSQL creates new physical row versions in the main table for this, InnoDB modifies rows in place and reconstructs older versions via undo logs, both approaches reach the same goal through different paths.

The price of this lock freedom is the need to clean up old versions once no active transaction needs them anymore, through vacuum in PostgreSQL, through the purge thread in InnoDB. Long-running transactions delay both processes and lead to table bloat or a growing history list respectively. Anyone who understands MVCC also understands why short transactions, regular monitoring of maintenance processes, and a deliberately chosen isolation level are not nice-to-haves but direct consequences of the underlying versioning architecture.

For day-to-day practice, this means: anyone planning a new database or operating an existing one should take autovacuum parameters and undo log configuration just as seriously as indexing or query tuning. A system that scales beautifully under MVCC as long as maintenance processes keep pace with write load can, with neglected configuration, run into noticeable performance problems within a few weeks, problems that only reveal themselves as bloat or a growing history list after thorough diagnosis.

Both monitoring signals, the dead tuple ratio in PostgreSQL and the history list length in InnoDB, therefore belong on every database dashboard alongside classic metrics such as query latency and connection utilization.

11. FAQ: Understanding MVCC