Understanding Multiversion Concurrency Control (MVCC) in InnoDB

1. What is Multiversion Concurrency Control

Multiversion Concurrency Control (MVCC) is a technique that controls concurrency by keeping snapshots of data at specific points in time, allowing each transaction to see a consistent view of the database independent of other concurrent transactions.

In simple terms, MVCC stores historical versions of rows and uses version numbers to decide which rows are visible to a transaction, enabling non‑locking reads while still providing isolation.

MVCC can be seen as a variant of row‑level locking that often avoids explicit locks, resulting in lower overhead for read‑heavy workloads.

Most transactional storage engines in MySQL (InnoDB, NDB, etc.) implement MVCC, as do other databases such as Oracle and PostgreSQL, though the exact mechanisms differ (optimistic vs. pessimistic concurrency control).

2. Problems Solved by MVCC

2.1 Eliminating read‑write blocking

MVCC allows reads and writes to proceed without blocking each other, improving transaction concurrency.

Evolution of concurrency control: Simple locks – only serial execution. Read‑write locks – enable concurrent reads. MVCC – enables concurrent reads and writes.

2.2 Reducing deadlock probability

InnoDB’s MVCC uses optimistic locking, so reads do not acquire locks and writes lock only the necessary rows.

2.3 Providing consistent (snapshot) reads

Snapshot reads return the state of the database as of the transaction’s start time, ignoring changes committed after that point.

3. Snapshot Read vs. Current Read

Snapshot read (SnapShot Read) is a non‑locking, consistent read and is a core reason for InnoDB’s high concurrency.

Consistency means a transaction sees either data that existed before it started or data it has itself inserted/modified.

Simple SELECT statements are snapshot reads. Example:

SELECT * FROM t WHERE id=1

Current read obtains the latest version of data, often requiring locks. Example of a locked SELECT:

SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM t WHERE id=1 FOR UPDATE;

4. How InnoDB Implements MVCC

4.1 Storing multiple versions of a row

Transaction ID (Version)

Each transaction receives a monotonically increasing transaction ID, which serves as the version number for ordering.

Hidden columns in each row

InnoDB adds three hidden fields to every row:

DB_ROW_ID : 6‑byte hidden row identifier used to build the default clustered index when none is defined.

DB_TRX_ID : 6‑byte identifier of the transaction that last inserted or updated the row.

DB_ROLL_PTR : 7‑byte pointer to the Undo Log entry that stores the previous version of the row.

Undo Log

The Undo Log keeps historical row versions. Each undo record stores the DB_TRX_ID of the transaction that created that version, forming a linked list of snapshots.

4.2 MVCC behavior under REPEATABLE READ isolation

SELECT

InnoDB returns rows that satisfy two conditions:

The row’s version is older than or equal to the current transaction’s version.

The row’s delete version is either undefined or newer than the current transaction’s version.

INSERT

New rows receive the current transaction’s version as their row version.

DELETE

Deleted rows are marked with the current transaction’s version; deletion is internally treated as an update that sets a special delete flag.

UPDATE

Updates create a new version of the row with the current transaction’s version and mark the old version as deleted using the same version number.

5. Summary

MVCC enables read‑write concurrency primarily under the REPEATABLE READ and READ COMMITTED isolation levels. It is incompatible with READ UNCOMMITTED (which always reads the latest rows) and SERIALIZABLE (which locks all rows). Features such as row locking, transaction rollback, and concurrency control are tightly coupled with MVCC.

References

MySQL 5.7 Documentation: innodb-multi-versioning "High Performance MySQL"