Master MySQL MVCC: Unlocking Concurrency, Locks, and Isolation Levels

Why MVCC Is Needed

Before MVCC, MySQL relied on lock mechanisms to resolve concurrent conflicts, which caused three major problems:

Blocking Wait : write locks block reads and vice‑versa.

Deadlock Risk : transactions can wait indefinitely for each other.

Low Concurrency : pessimistic locking limits throughput.

-- Traditional lock contention 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

Because a write can block all reads, performance suffers.

MVCC’s core idea is to maintain multiple versions for each row so reads and writes can proceed without blocking each other.

Core MVCC Concepts

Non‑blocking Reads : reads do not wait for writes.

Non‑blocking Writes : writes do not wait for reads (under appropriate isolation).

High Concurrency : system throughput increases dramatically.

Transaction Isolation Levels

Isolation levels define how MVCC behaves. MySQL supports four levels, each with a specific SET SESSION TRANSACTION ISOLATION LEVEL … command and visibility rules.

Read Uncommitted

Allows dirty reads. No locks are taken; the latest version is read regardless of commit status.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Performance is best but data consistency is worst; rarely used in production.

Read Committed

Prevents dirty reads. Each query creates a new snapshot (Read View) that sees only committed data.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

However, non‑repeatable reads and phantom reads can still occur.

Repeatable Read (default)

Prevents dirty and non‑repeatable reads. A single Read View is created at the start of the transaction and reused for all subsequent queries.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Phantom reads are still possible, but InnoDB mitigates most of them using Next‑Key Locking.

Serializable

Eliminates all three anomalies by converting the engine to a strict lock‑based protocol; every read acquires a shared lock, drastically reducing concurrency.

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Common Anomalies

Dirty Read

A transaction reads data modified by another uncommitted transaction.

Tx A updates a row but does not commit.

Tx B reads the updated value.

Tx A rolls back, leaving Tx B with invalid data.

Non‑Repeatable Read

The same row yields different values in two reads within one transaction because another transaction committed an update in between.

Tx A reads balance = 100.

Tx B updates balance to 150 and commits.

Tx A reads again and sees 150.

Phantom Read

The result set size changes between two identical queries because another transaction inserted or deleted rows.

Tx A queries employees < 30 years → 10 rows.

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

Tx A repeats the query → 11 rows.

MVCC Architecture in InnoDB

MVCC relies on three components:

Hidden Fields : each row stores trx_id (transaction ID) and roll_ptr (pointer to previous version). row_id is used only when no primary key exists.

Undo Log : records the before‑image of every change. Two types exist:

INSERT Undo Log – deleted on rollback.

UPDATE Undo Log – used for rollback and MVCC visibility.

Read View : defines which versions are visible to a transaction.

Data row physical layout:

+------------+-------------+---------------+----------+-----------+
| Header     | DB_TRX_ID   | DB_ROLL_PTR   | col1     | col2      |
+------------+-------------+---------------+----------+-----------+
| 5 bytes    | 6 bytes    | 7 bytes       | variable | variable |
+------------+-------------+---------------+----------+-----------+

Read View Mechanics

A Read View contains: low_limit_id: high water‑mark – transactions with IDs ≥ this are invisible. up_limit_id: low water‑mark – transactions with IDs < this are visible. creator_trx_id: ID of the transaction that created the view. ids: list of active transaction IDs at view creation.

Visibility algorithm (simplified):

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

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

Read View generation differs by isolation level:

Read Committed : a new Read View is built for every SELECT.

Repeatable Read : a single Read View is created when the transaction starts and reused.

Read Committed Example

-- Tx1
BEGIN;
SELECT name FROM users WHERE id = 1; -- ReadView1 sees version 100

-- Tx2 updates and commits
UPDATE users SET name = 'Charlie' WHERE id = 1;
COMMIT;

-- Tx1 selects again
SELECT name FROM users WHERE id = 1; -- ReadView2 now sees version 200

Repeatable Read Example

-- Tx1
BEGIN;
SELECT name FROM users WHERE id = 1; -- ReadView created, sees version 100

-- Tx2 updates and commits
UPDATE users SET name = 'Charlie' WHERE id = 1;
COMMIT;

-- Tx1 selects again (same ReadView) → still sees version 100
SELECT name FROM users WHERE id = 1;

Interview Checklist for MVCC

Explain what MVCC is and why MySQL uses it.

Describe the hidden fields, undo log, and read view.

State how MySQL controls concurrent access in InnoDB.

Discuss whether a thread can read data while another thread modifies it.

Justify when to use Read Committed versus Repeatable Read.

MVCC is MySQL InnoDB’s protocol for controlling concurrent data access, built on version chains stored in the undo log and governed by transaction‑specific read views.