Understanding MySQL InnoDB MVCC: Concepts, Isolation Levels, and Code Implementation

Introduction

Multi-Version Concurrency Control (MVCC) is a technique used to improve database concurrency performance, especially under high‑read/write workloads. By maintaining multiple versions of data, MVCC avoids read‑write conflicts, allowing reads to proceed without blocking writes and vice versa. The following sections detail how MySQL InnoDB implements MVCC.

Basic Concept of MVCC

MVCC can be seen as an improvement over row‑level locking. It stores several versions of a row at different points in time, enabling concurrent access. MVCC is often used to implement optimistic locking by controlling consistency with version numbers, thus eliminating lock‑induced bottlenecks.

Why can reads and writes run without blocking each other?

Before MVCC, databases relied on locks to guarantee isolation, meaning a transaction reading a row would prevent other transactions from modifying that row until the read completed. MVCC removes this restriction, dramatically increasing transaction concurrency.

Example Scenario

Consider two sessions:

Session 1 updates a record but does not commit yet. What does Session 2 see when it queries the same record?

After Session 1 commits, how does Session 2's result change?

The answer depends on the transaction isolation level:

READ‑UNCOMMITTED : Session 2 sees the updated value column= lisi even before Session 1 commits.

READ‑COMMITTED : Session 2 sees the original value column= zhangsan before commit, and the updated value after commit.

REPEATABLE‑READ and SERIALIZABLE : Session 2 always sees the original value column= zhangsan until the transaction ends.

Isolation Level Commands (MySQL 5.7 vs 8.0)

Operation

MySQL 5.7

MySQL 8.0

Query current session isolation level

SELECT @@tx_isolation; or SHOW VARIABLES LIKE 'transaction_isolation';

SELECT @@transaction_isolation; or SHOW VARIABLES LIKE 'transaction_isolation';

Query global isolation level

SELECT @@global.tx_isolation; or SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';

SELECT @@global.transaction_isolation; or SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';

Set current session isolation level

SET SESSION TRANSACTION ISOLATION LEVEL <level>;

SET SESSION TRANSACTION ISOLATION LEVEL <level>;

Set global isolation level

SET GLOBAL TRANSACTION ISOLATION LEVEL <level>;

SET GLOBAL TRANSACTION ISOLATION LEVEL <level>;

The possible isolation levels are:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ (MySQL default)

SERIALIZABLE

How InnoDB Ensures Consistent Reads

InnoDB relies on Undo logs and MVCC. The three hidden columns stored in each row are:

DB_TRX_ID : the transaction ID that last inserted or updated the row.

DB_ROLL_PTR : a rollback pointer to the previous version of the row.

DB_ROW_ID : an auto‑increment row identifier used when no explicit primary key exists.

These columns together enable InnoDB to keep multiple versions of a row and allow reads to see a consistent snapshot.

Read View

A Read View is a snapshot of the database state at the moment a transaction starts. It records:

trx_ids : IDs of all active (uncommitted) transactions at snapshot creation.

low_limit_id : the next transaction ID to be assigned (transactions with IDs ≥ this are invisible).

up_limit_id : the smallest committed transaction ID at snapshot creation (transactions with IDs < this are visible).

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

Visibility of a row is determined by comparing the row's TRX_ID with these fields.

Decision Process

If the row's TRX_ID is less than low_limit_id , the row was committed before the snapshot and is visible.

If the row's TRX_ID is greater than or equal to up_limit_id , the row was created after the snapshot and is invisible; the engine follows the rollback pointer to an older version.

If the row's TRX_ID falls within the snapshot range, further checks are needed: If the ID appears in trx_ids , the transaction is still active and the row is invisible; follow the rollback pointer. If the ID is not in trx_ids , the transaction has already committed and the row is visible.

Current Read vs. Snapshot Read

Current Read : obtains the latest version of a row and may lock it (e.g., SELECT ... LOCK IN SHARE MODE , SELECT ... FOR UPDATE , UPDATE , INSERT , DELETE ).

Snapshot Read : a non‑locking SELECT that uses the Read View to return the data as of the snapshot, ensuring consistency without blocking other transactions.

In the SERIALIZABLE level, snapshot reads degrade to current reads to guarantee full consistency.

Source Code Analysis

read_view_open_now Function

read_view_t* read_view_open_now(trx_t* trx) {
    read_view_t* view;
    view = static_cast
(ut_malloc_nokey(sizeof(*view)));
    ut_a(view != NULL);
    /* set creator transaction ID */
    view->creator_trx_id = trx->id;
    /* get active transaction list */
    view->m_trx_ids = trx_sys_get_active_trx_ids();
    /* number of active transactions */
    view->trx_list_len = trx_sys->rw_trx_list_len;
    /* low water mark */
    view->low_limit_no = trx_sys->rw_trx_list->start->id;
    /* high water mark */
    view->up_limit_no = trx_sys->rw_trx_list->end->id;
    return(view);
}

trx_sys_get_active_trx_ids Function

trx_id_t* trx_sys_get_active_trx_ids() {
    trx_id_t* trx_ids;
    trx_ids = static_cast
(ut_malloc_nokey(trx_sys->rw_trx_list_len * sizeof(trx_id_t)));
    rw_trx_list_lock();
    rw_trx_t* rw_trx = trx_sys->rw_trx_list->start;
    for (size_t i = 0; i < trx_sys->rw_trx_list_len; i++, rw_trx = rw_trx->next) {
        trx_ids[i] = rw_trx->id;
    }
    rw_trx_list_unlock();
    return trx_ids;
}

read_view_sees_trx_id Function

bool read_view_sees_trx_id(read_view_t* view, trx_id_t trx_id) {
    /* visible if transaction ID is less than up_limit_no */
    if (trx_id < view->up_limit_no) {
        return true;
    }
    /* invisible if transaction ID is >= low_limit_no */
    if (trx_id >= view->low_limit_no) {
        return false;
    }
    /* invisible if ID is in the active list */
    for (size_t i = 0; i < view->trx_list_len; i++) {
        if (view->m_trx_ids[i] == trx_id) {
            return false;
        }
    }
    return true;
}

read_view_close Function

void read_view_close(read_view_t* view) {
    ut_free(view->m_trx_ids);
    ut_free(view);
}

Code Logic Summary

Create Read View ( read_view_open_now ): allocate a view, record creator ID, capture active transaction IDs, and set low/high limits.

Get Active Transaction IDs ( trx_sys_get_active_trx_ids ): iterate over the active transaction list and store each ID.

Determine Visibility ( read_view_sees_trx_id ): compare a row's TRX_ID against the view's limits and active list to decide if the row is visible to the current transaction.

Close Read View ( read_view_close ): free allocated memory.

Preparing Test Data

CREATE TABLE test_mvcc (
    id INT PRIMARY KEY,
    value VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO test_mvcc (id, value) VALUES (1, 'Initial Value');

Operations in Two Sessions

Session 1 (starts transaction and updates the row) :

START TRANSACTION;
UPDATE test_mvcc SET value = 'Updated Value by Session 1' WHERE id = 1;
-- transaction not yet committed, changes visible only to session 1

Session 2 (starts transaction and reads the row) :

START TRANSACTION;
SELECT * FROM test_mvcc WHERE id = 1;
-- should still see 'Initial Value' because session 1 has not committed

Commit Transaction and Observe Changes

Session 1 commits:

COMMIT;

Session 2 queries again:

SELECT * FROM test_mvcc WHERE id = 1;
-- now sees 'Updated Value by Session 1'

Analyzing Visibility

The experiment demonstrates how isolation levels affect what a transaction can see. In READ‑COMMITTED , each SELECT creates a new Read View, leading to non‑repeatable reads. In REPEATABLE‑READ , the first SELECT creates the Read View, and subsequent reads within the same transaction see a stable snapshot.

Source: juejin.cn/post/7410616997007163432

Note: The article also contains promotional material for a backend‑focused technical community, but the core content remains an educational explanation of MySQL MVCC.