Why MySQL Uses MVCC: A Deep Dive into Concurrency, Isolation Levels, and Read Views

What is MVCC and why MySQL needs it

MVCC (Multi‑Version Concurrency Control) is the protocol InnoDB uses to allow concurrent reads and writes without blocking each other. Before MVCC MySQL relied on various lock mechanisms (global, table, row, gap, next‑key) which cause blocking waits, deadlocks and low concurrency.

-- Traditional lock problem example
-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- acquire write lock

-- Transaction 2 (blocked)
BEGIN;
SELECT balance FROM accounts WHERE user_id = 1; -- waits for read lock until Tx1 commits

MVCC keeps multiple versions of each row so that reads can see a consistent snapshot while writes create new versions.

Non‑blocking reads : a SELECT does not wait for concurrent writes.

Non‑blocking writes : a write does not wait for concurrent reads (under reasonable isolation levels).

High concurrency : throughput is dramatically improved.

Transaction isolation levels

Isolation levels determine how snapshots (Read Views) are created and which anomalies are prevented.

Read Uncommitted

Dirty reads are possible.

No locks are taken; the latest page is read regardless of commit status.

Set with SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Read Committed (RC)

Dirty reads are avoided; each SELECT sees the latest committed snapshot.

A new Read View is created for every SELECT.

Set with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Repeatable Read (RR) – MySQL default

Dirty reads and non‑repeatable reads are avoided; phantom reads can still occur.

A single Read View is created at the first SELECT of the transaction and reused.

Set with

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Transaction A (RR)
BEGIN;
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE; -- locks range
INSERT INTO users (name, age) VALUES ('NewUser', 25); -- blocked until A commits

Serializable

All reads and writes are serialized; no MVCC is used.

Provides the strongest consistency but lowest concurrency.

Set with

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Anomalies explained

Dirty Read : a transaction reads uncommitted changes from another transaction.

Tx A updates balance to 200 (uncommitted).

Tx B reads 200.

Tx A rolls back, restoring balance to 100. Tx B has acted on invalid data.

Non‑Repeatable Read : the same row yields different values within one transaction because another transaction committed an update.

Tx A reads balance = 100.

Tx B commits an update to 150.

Tx A reads again and sees 150.

Phantom Read : the result‑set size changes because another transaction inserted or deleted rows.

Tx A queries employees < 30, gets 10 rows.

Tx B inserts a 25‑year‑old employee and commits.

Tx A repeats the query and now gets 11 rows.

MVCC architecture

InnoDB implements MVCC with three core components:

Hidden fields stored with each row: trx_id (the ID of the transaction that last modified the row) and roll_ptr (pointer to the previous version in the undo log).

Undo Log version chain that records previous versions for rollback and visibility.

Read View – a snapshot that defines which versions are visible to a transaction.

Hidden fields

Each row stores: trx_id: transaction ID of the last modifier. roll_ptr: pointer to the previous version in the undo log. row_id: internal row identifier (not used by MVCC).

+------------+-------------+---------------+----------+-----------+
| HeaderInfo | DB_TRX_ID   | DB_ROLL_PTR   | col1     | col2      |
+------------+-------------+---------------+----------+-----------+
| 5 bytes    | 6 bytes     | 7 bytes       | varlen   | varlen    |
+------------+-------------+---------------+----------+-----------+

Undo Log

The undo log stores the before‑image of each modification:

INSERT Undo Log : records inserted rows; on rollback the rows are deleted.

UPDATE Undo Log : records old versions for rollback and MVCC visibility.

Read View

A Read View contains visibility rules for a transaction.

class ReadView {
private:
    trx_id_t m_low_limit_id;   // high water mark: IDs >= this are invisible
    trx_id_t m_up_limit_id;    // low water mark: IDs < this are visible
    trx_id_t m_creator_trx_id; // ID of the transaction that created this view
    ids_t   m_ids;             // list of active transaction IDs
    trx_id_t m_low_limit_no;   // used for purge
};

Visibility is determined by four rules:

def check_visibility(trx_id, read_view):
    # Rule 1: own changes are always visible
    if trx_id == read_view.creator_trx_id:
        return True
    # Rule 2: IDs smaller than low water mark are committed before view
    if trx_id < read_view.up_limit_id:
        return True
    # Rule 3: IDs >= high water mark started after view
    if trx_id >= read_view.low_limit_id:
        return False
    # Rule 4: active transactions are invisible
    if trx_id in read_view.ids:
        return False
    return True  # committed transaction

def find_visible_version(version_chain, read_view):
    for version in version_chain:
        if check_visibility(version.trx_id, read_view):
            return version
    return None  # no visible version

Behavior under different isolation levels

Read Committed

Each SELECT creates a new Read View, so the transaction sees the latest committed data each time.

-- Tx1 (RC)
BEGIN;
SELECT name FROM users WHERE id = 1; -- sees version 100
UPDATE users SET name='Charlie' WHERE id = 1; COMMIT;
SELECT name FROM users WHERE id = 1; -- sees new version 200

Repeatable Read

A single Read View is created at the first SELECT and reused, guaranteeing the same snapshot for the whole transaction.

-- Tx1 (RR)
BEGIN;
SELECT name FROM users WHERE id = 1; -- sees version 100 (Read View created)
UPDATE users SET name='Charlie' WHERE id = 1; COMMIT;
SELECT name FROM users WHERE id = 1; -- still sees version 100

Isolation‑level anomaly matrix (textual)

Read Uncommitted : dirty read ✔, non‑repeatable read ✔, phantom read ✔ (all possible).

Read Committed : dirty read ✖, non‑repeatable read ✔, phantom read ✔.

Repeatable Read : dirty read ✖, non‑repeatable read ✖, phantom read ✔ (possible, but InnoDB’s next‑key lock mitigates most cases).

Serializable : all three anomalies are prevented.

Additional concepts

Snapshot read : reads a consistent snapshot taken at transaction start.

Current read : reads the latest committed version; in InnoDB this is similar to snapshot read under Repeatable Read.

Interview checklist (technical)

Explain MVCC’s purpose and how hidden fields, undo‑log chain, and Read View work together.

Describe the four isolation levels, their SQL settings, and which anomalies each prevents.

Show how non‑blocking reads and writes are achieved with example SQL.

Discuss trade‑offs: higher isolation improves consistency but reduces concurrency.