Understanding MVCC (Multiversion Concurrency Control) in MySQL InnoDB

When thinking about concurrency control, many first consider locking, but MySQL also offers a lock‑free solution called MVCC (Multiversion Concurrency Control).

MVCC allows three concurrency scenarios—read‑read, read‑write, and write‑write. Read‑read is safe without locks, while write‑write typically uses locks. Read‑write conflicts are resolved by MVCC.

Snapshot read returns the data as of the moment the snapshot was taken; a normal SELECT without locking is a snapshot read. Current read reads the latest data and requires locking (e.g., SELECT * FROM xx_table LOCK IN SHARE MODE , SELECT * FROM xx_table FOR UPDATE , INSERT INTO xx_table ... , UPDATE xx_table ... , DELETE FROM xx_table ... ).

The snapshot data is stored in the undo log. The undo log records the before‑image of each row before it is modified, enabling rollback or crash recovery.

Each row also contains implicit fields: db_row_id (hidden primary key), db_trx_id (ID of the transaction that last modified the row), and db_roll_ptr (pointer to the previous version in the undo log).

These fields form a chain of snapshots. To determine which snapshot a transaction should see, InnoDB builds a Read View containing:

trx_ids – list of currently active (uncommitted) transaction IDs

low_limit_id – smallest active transaction ID

up_limit_id – largest active transaction ID

creator_trx_id – ID of the transaction that created the Read View

Visibility rules compare a row’s db_trx_id with the Read View:

If db_trx_id < up_limit_id , the row is visible.

If db_trx_id > low_limit_id , the row is invisible.

If up_limit_id > db_trx_id > low_limit_id , the row is visible only if its db_trx_id is not in trx_ids .

When a row is invisible, InnoDB walks the undo log to find an older snapshot that satisfies the visibility criteria; if none is found, the query returns no row.

MVCC works together with transaction isolation levels. In the Read Committed (RC) level, a new Read View is created for each SELECT, while in Repeatable Read (RR) a single Read View is created on the first SELECT of a transaction, preventing non‑repeatable reads.

Thus, InnoDB implements MVCC through the combination of Read View and Undo Log, where the undo log stores historical snapshots and the Read View decides which snapshot is visible to a transaction.

References: MySQL Documentation – InnoDB Undo Logs Geekbang Article on MVCC CSDN Explanation of MVCC 51CTO MVCC Overview Zhihu Discussion on MVCC