Mastering MySQL MVCC: How InnoDB Achieves Consistent Reads

Preface

Hello, I am Su San.

In the previous MySQL article I covered transaction properties, isolation levels, and concurrency consistency, mentioning the four isolation levels and that MVCC implements read‑committed and repeatable‑read isolation.

MVCC is a long‑standing topic in MySQL, but because it operates at a low level most developers never interact with it directly, so many interviewees only have a vague idea.

This article explains MVCC in detail.

What is MVCC?

MVCC (Multi‑Version Concurrency Control) is an optimistic‑locking method that enables read‑committed and repeatable‑read isolation. In MySQL it is implemented by the InnoDB storage engine, not by MySQL itself, and different engines have different implementations.

When multiple transactions read the same row, each transaction works with its own version of the row; InnoDB records a version chain to allow concurrent SELECTs.

MVCC works by creating a new version snapshot for every write (INSERT, UPDATE, DELETE) while reads access older snapshots, so reads and writes are independent.

Core

Undo Log

Undo Log is one of MySQL’s three log types. It stores previous versions of rows so a transaction can roll back or recover past data.

In a clustered index record InnoDB stores two hidden fields: trx_id (the ID of the transaction that last modified the row) and roll_pointer (a pointer to the previous version stored in the Undo Log).

For example, consider an employee table with columns id, name, and age. Transaction A inserts a row with id=1, name='fancy', age=25. The row’s trx_id records A’s transaction ID. When transaction B later updates the same row, a new Undo Log entry is created, trx_id is updated to B’s ID, and roll_pointer points to the previous version.

Read View

Read View holds a list of active transactions to decide whether a version is visible to the current transaction. It contains four important fields:

creator_trx_id : the ID of the transaction that created the Read View.

m_ids : IDs of all currently active (uncommitted) transactions.

min_trx_id : the smallest transaction ID in m_ids.

max_trx_id : the next transaction ID that InnoDB will assign.

Visibility is determined by comparing a row’s trx_id with these fields. If the row’s trx_id is less than the current transaction’s creator_trx_id and not present in m_ids, the row is visible.

How MVCC Implements Repeatable Read

Assume transaction A (ID 20) and transaction B (ID 30) both operate on the row with id=1. Each creates its own Read View. Transaction A reads the row, sees trx_id=10, which is less than its creator_trx_id, so the row is visible and A can safely read it.

When B updates the row, a new Undo Log entry is created, trx_id becomes 30, and roll_pointer points to the version created by A. If A later reads the same row again, it finds trx_id=30. Because 20 < 30 < 31 (the max_trx_id) and 30 appears in m_ids, A knows the row was modified by a concurrent active transaction and must fetch the previous version via the Undo Log chain until it reaches a version whose trx_id is ≤ 20 and not in m_ids.

How MVCC Implements Read Committed

Read‑committed isolation prevents dirty reads. If transaction B updates a row but does not commit, its trx_id will appear in the current transaction’s m_ids. When transaction A reads the row, it sees that the row’s trx_id is greater than its creator_trx_id and that the modifying transaction is still active, so A skips the uncommitted version and follows the Undo Log to the latest committed version.

Summary

In InnoDB, MVCC combines an Undo Log that stores historical snapshots with a Read View that defines visibility rules. Together they enable read‑committed and repeatable‑read isolation without acquiring locks. Solving phantom reads, however, also requires next‑key (gap) locks, which will be covered in a future article.