Understanding MySQL Isolation Levels and MVCC: A Deep Dive

Preface

When we connect to a MySQL server we start a session. Each session can start transactions, and the server may have multiple concurrent transactions. To balance isolation and performance, some isolation guarantees must be sacrificed.

Isolation Issues

Four phenomena appear when transactions are not executed serially: Dirty write: a transaction modifies data that another uncommitted transaction has also modified. Dirty read: a transaction reads data modified by an uncommitted transaction. Non‑repeatable read: a transaction reads a row, another transaction commits a change to that row, and the first transaction reads a different value later. Phantom: a transaction’s range query later sees rows inserted by another transaction that were not present before.

-- Session A
BEGIN;
SELECT * FROM trace WHERE key = '20041';
-- returns 'key'

-- Session B
UPDATE trace SET typeof = 'buttonkey' WHERE key = '20041';
COMMIT;

-- Session A reads again
SELECT * FROM trace WHERE key = '20041';
-- now returns 'buttonkey'

Standard Isolation Levels

READ UNCOMMITTED

: allows dirty reads, dirty writes, and phantoms. READ COMMITTED: prevents dirty reads and writes, but allows non‑repeatable reads and phantoms. REPEATABLE READ: prevents dirty reads, non‑repeatable reads, and dirty writes; phantoms may still occur. SERIALIZABLE: prevents all four phenomena by using locking.

Isolation Level | Dirty Write | Dirty Read | Non‑Repeatable Read | Phantom
----------------|------------|-----------|--------------------|--------
READ UNCOMMITTED| ✅         | ✅        | ✅                 | ✅
READ COMMITTED  | ❌         | ❌        | ✅                 | ✅
REPEATABLE READ | ❌         | ❌        | ❌                 | ✅
SERIALIZABLE   | ❌         | ❌        | ❌                 | ❌

MVCC (Multi‑Version Concurrency Control)

MVCC implements optimistic locking by using an undo log to keep previous versions of rows. Two read modes exist: Snapshot read: reads the version visible to the current transaction (simple SELECT). Current read: reads the latest committed version and acquires locks for INSERT/UPDATE/DELETE operations.

Version Chain

Each InnoDB clustered index record stores hidden columns trx_id (the transaction that created the version) and roll_pointer (a pointer to the previous version stored in the undo log). As updates occur, a linked list of old versions is built, with the head pointing to the newest value.

trx389:
BEGIN;
UPDATE trace SET type='mainkey' WHERE key='20041';
COMMIT;

trx399:
BEGIN;
UPDATE trace SET type='buttonkey' WHERE key='20041';
COMMIT;

Read View

A read view is created by MySQL to determine which versions are visible to a transaction. It consists of: m_ids: list of currently active transaction IDs. min_trx_id: the smallest active transaction ID. max_trx_id: the next transaction ID to be assigned. create_trx_id: the transaction ID that created the read view.

Only when a transaction performs INSERT, UPDATE, or DELETE is a trx_id allocated; read‑only transactions keep trx_id at 0.

Isolation Level Comparison

In READ COMMITTED, a new read view is generated before each read, so the transaction may see changes committed by other transactions between reads. In REPEATABLE READ, a single read view is created on the first read and reused for the rest of the transaction, guaranteeing that repeated reads return the same result.

Summary

MVCC enables MySQL to provide consistent snapshots for SELECT statements under READ COMMITTED and REPEATABLE READ isolation levels by leveraging the undo log, version chains, and read views. Proper understanding of these mechanisms helps avoid dirty writes, dirty reads, non‑repeatable reads, and phantom reads.