MVCC Principles

What is MVCC?

Multi-Version Concurrency Control (MVCC) is the technology that allows InnoDB to perform "Lock-free Reads." By maintaining multiple versions of a data row, one transaction can read a "snapshot" of the data from the past while another transaction is currently modifying it.

The Goal: Read-Write concurrency. Readers never block writers, and writers never block readers.

The Three Components of MVCC

1. Hidden Columns

Every row in an InnoDB table has three hidden metadata fields:

• DB_TRX_ID: The ID of the last transaction that modified this row.
• DB_ROLL_PTR: A "Rollback Pointer" that points to the previous version of this row in the Undo Log.
• DB_ROW_ID: An internal row ID (used if no primary key exists).

2. The Undo Log Version Chain

As rows are updated, InnoDB doesn't overwrite the old data in the undo log. Instead, it links them together.

Current Row:  [ name: "Eva", trx_id: 300 ] ──┐
                                             ▼
Undo Log v2:  [ name: "Cid", trx_id: 200 ] ──┐
                                             ▼
Undo Log v1:  [ name: "Bob", trx_id: 100 ] ──▶ NULL

3. The Read View

A Read View is a "snapshot of active transactions" created when a user performs a SELECT. It contains:

• m_ids: A list of all transaction IDs that are currently running (uncommitted).
• min_trx_id: The smallest ID in m_ids.
• max_trx_id: The next transaction ID to be assigned (Largest ID + 1).
• creator_trx_id: The ID of the transaction that created this view.

Visibility Logic (The "Who can see what" rules)

When a transaction reads a row with trx_id = T, it follows these rules:

1. Is it me? If $T == creator_trx_id$, the change is visible.
2. Is it old? If $T < min_trx_id$, the transaction that made the change was already committed before we started. Visible.
3. Is it from the future? If $T \ge max_trx_id$, the change was made by a transaction that started after we created our Read View. Invisible.
4. Is it active? If $T$ is inside the m_ids list, the transaction is still running and hasn't committed yet. Invisible.

If a version is invisible, InnoDB follows the DB_ROLL_PTR to the next version in the chain and repeats the check until it finds a visible version.

Snapshot Read vs. Current Read

Deep Technical Insights

The Impact of Long-Running Transactions

If a transaction stays open for a long time (e.g., a multi-hour report), it prevents MySQL from cleaning up (Purging) the Undo Logs. Every version of every row modified since that transaction started must be kept in the Undo Log, leading to disk space bloat and potentially slowing down every other query in the system as they "walk" through massive version chains.

RC vs. RR in MVCC

• In Read Committed, a fresh Read View is generated for every individual SELECT statement.
• In Repeatable Read, the Read View is generated only for the first SELECT statement and is reused for all subsequent queries in that transaction, ensuring the same data is seen every time.