Understanding MySQL MVCC Mechanism

Explain the MVCC mechanism 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 eliminates dirty reads.

The protection is achieved through MVCC (multi‑version concurrency control), which allows read‑write and write‑read operations to proceed without conflict.

Multiple Versions

Each row can have several versions after updates, forming a version chain similar to a string of candied fruit. For example, a bank account record may have multiple modifications, each tagged with the transaction that performed it.

Example version chain (using undo log):

Account A = initial 200 (trx id 40)

Account A = 200 + 100 = 300 (trx id 51)

Account A = 300 + 50 = 350 (trx id 59)

Account A = 350 – 30 = 320 (trx id 72)

MySQL links these versions via the undo log.

Control

MySQL compares the transaction ID of each version with the IDs stored in the current transaction’s ReadView. If the version satisfies the ReadView criteria, it is returned.

ReadView

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

When is ReadView generated?

Each query creates a ReadView. In RC level a new ReadView is generated for every query; in RR level the ReadView remains constant for the whole transaction.

ReadView contains four important fields:

m_ids : list of active (uncommitted) transaction IDs

min_trx_id : smallest ID in m_ids

max_trx_id : next transaction ID to be generated (i.e., the maximum + 1)

creator_trx_id : ID of the current transaction

How are these fields used?

Assume transaction A (trx 51) reads while transaction B (trx 59) updates but has not committed. The ReadView for A looks like:

Active transaction list = [51, 59]; min ID = 51; max ID = 60; current ID = 51.

Transaction A scans the version chain and applies three rules:

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

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

If the ID is between min and max, further check:

How RC reads committed data

In RC, each new query regenerates ReadView, so transaction A can see B’s committed changes after B commits, because B’s ID (59) is no longer in the active list.

How RR achieves repeatable reads

RR keeps the same ReadView for the whole transaction, so even after B commits, A continues to use the original ReadView and cannot see B’s new version, guaranteeing repeatable reads.

How RR avoids phantom reads

When A performs a range query, any rows inserted by other transactions after A’s ReadView (e.g., trx 72) have IDs ≥ max_trx_id and are therefore invisible, preventing phantom reads.

Summary

By combining version chains with ReadView, MySQL’s MVCC prevents dirty reads, non‑repeatable reads, and phantom reads under RR, and eliminates dirty reads while allowing reads of committed data under RC.