Multi-Version Concurrency Control (MVCC) is a concurrency control technique where writers create new versions of data instead of overwriting existing values in place. Each write produces a new version tagged with the writing transaction's ID, while old versions are preserved. Readers access the version consistent with their snapshot, meaning they see a consistent point-in-time view of the data without ever needing to acquire read locks.

The Core Principle

The fundamental guarantee of MVCC is: readers never block writers, and writers never block readers. A read-only query can scan millions of rows while concurrent transactions insert and update data freely. The reader simply ignores versions created by transactions that started after its snapshot. Writers only block other writers when they attempt to modify the same row (write-write conflict).

Version Chains

Each logical row in an MVCC database has a version chain: a sequence of physical row versions, each tagged with the transaction ID that created it. When a transaction updates a row, it creates a new version and links it into the chain. The chain may be ordered newest-first or oldest-first depending on the implementation.

Visibility Check

When a transaction reads a row, it walks the version chain and selects the most recent version that is visible according to its snapshot. A version is visible if:

1. The version's creating transaction committed before the reader's snapshot.
2. The version's creating transaction is not in the reader's active-transaction set (xip[]).
3. The version has not been deleted (or the deleting transaction has not yet committed from the reader's perspective).

PostgreSQL's MVCC Implementation

PostgreSQL stores all versions directly in the table's heap pages. Each tuple (row version) has two hidden columns:

• xmin: the transaction ID that created this version (via INSERT or UPDATE).
• xmax: the transaction ID that deleted or replaced this version (via DELETE or UPDATE). If xmax is 0 or the deleting transaction has not committed, the version is still "live."

When a transaction updates a row, PostgreSQL inserts a new tuple with the new values and sets the old tuple's xmax to the updating transaction's ID. The old tuple remains in the heap until it is removed by VACUUM.

InnoDB's MVCC Implementation

MySQL InnoDB takes a different approach. The primary table always stores only the latest committed version of each row. Older versions are stored in the undo log (rollback segment). When a reader needs to see an older version, InnoDB follows undo pointers from the current row backward through the undo log to reconstruct the version visible at the reader's snapshot. This is called a consistent read.

InnoDB's read view is its snapshot equivalent. It records: the list of active transaction IDs, the low-water mark (oldest active txnId), and the high-water mark (next txnId to be assigned). The visibility check compares the row's creating transaction ID against these boundaries.

Write-Write Conflicts

MVCC does not eliminate all conflicts. When two transactions attempt to update the same row, only one can succeed. The second writer either blocks (waiting for the first to commit or abort) or receives an error, depending on the isolation level and database implementation.