Understanding MySQL MVCC Mechanism and ReadView

Hello, I am Wu Fan. Today I bring a big‑company interview question: Explain MySQL's MVCC mechanism.

What is MVCC in MySQL?

MySQL provides four isolation levels. The Repeatable Read (RR) level prevents dirty reads, non‑repeatable reads, and phantom reads, while the Read Committed (RC) level only prevents dirty reads.

It achieves this by using Multi‑Version Concurrency Control (MVCC), which allows reads and writes to proceed without conflict.

Multi‑Version

Each row can have multiple versions after updates. These versions are linked together in a version chain, similar to a串糖葫芦 (candied haw). The chain records which transaction created each version.

For example, a bank account record might evolve as follows:

(1) Account A = initial value 200 yuan, transaction id = 40 →

(2) Account A = 200 yuan + 100 yuan = 300 yuan, transaction id = 51 →

(3) Account A = 300 yuan + 50 yuan = 350 yuan, transaction id = 59 →

(4) Account A = 350 yuan – 30 yuan = 320 yuan, transaction id = 72

MySQL uses the undo log to link these versions together.

Below is a visual of the undo‑log version chain:

Control

The engine compares the current transaction’s ID with the IDs stored in other transactions using a structure called ReadView. ReadView records the set of active transactions at the moment the view is created, and MySQL uses this information together with the version chain to decide which version is visible.

ReadView

ReadView guarantees that a transaction can only see data committed before the transaction started, or its own changes, thus avoiding dirty reads.

When is ReadView generated?

Each query generates its own ReadView. In RC level a new ReadView is created for every query; in RR level the ReadView remains constant for the whole transaction.

ReadView contains four important fields:

• m_ids : list of active transaction IDs that have not yet committed. • min_trx_id : the smallest transaction ID in m_ids . • max_trx_id : the next transaction ID to be generated (i.e., the current maximum + 1). • creator_trx_id : the ID of the transaction that created the ReadView.

How are these fields used?

Assume transaction A (ID 51) performs a query while transaction B (ID 59) updates data, both still uncommitted. The ReadView for A looks like:

Active transaction list = [51, 59]; min ID = 51; max ID = 60 (next ID); creator ID = 51.

Transaction A walks the version chain and applies three rules:

If a version’s transaction ID < min ID, the row was committed before any active transaction and is visible.

If a version’s transaction ID ≥ max ID, the row was created after the ReadView and is invisible.

If a version’s transaction ID is between min and max, MySQL checks whether the ID appears in m_ids . If it does, the version is from an active transaction and is invisible; otherwise it is from a committed transaction and is visible.

How does RC achieve “read‑committed”?

In RC, each query creates a fresh ReadView, so when transaction A runs a new query it sees the latest committed versions (e.g., transaction B’s ID 59 is no longer in the active list, making its version visible).

How does RR avoid non‑repeatable reads?

In RR the ReadView is fixed for the whole transaction, so subsequent queries see the same snapshot even if other transactions commit new data. This guarantees repeatable reads but may return stale data.

How does RR prevent phantom reads?

Consider transaction A querying rows with age > 10 and retrieving row X. Transaction C inserts a new row Y with age > 10 (ID 72). Because Y’s version ID (72) > max ID (60) in A’s ReadView, A cannot see Y, thus phantom reads are avoided.

Summary

By combining version chains with ReadView, MySQL prevents dirty reads, non‑repeatable reads, and phantom reads under the Repeatable Read isolation level, and prevents dirty reads while allowing “read‑committed” behavior under the Read Committed level.