Understanding MySQL Transactions and MVCC: From ACID to Isolation Levels

Transaction Definition and ACID Properties

A transaction is a user‑defined unit of database operations that must be executed completely or not at all. The four ACID properties are:

Atomicity : the transaction is indivisible.

Consistency : the database moves from one consistent state to another.

Isolation : concurrent transactions do not interfere with each other.

Durability : once committed, changes are permanent.

Common Concurrency Problems

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

Non‑repeatable read : a row read twice yields different values because another transaction modified it in between.

Phantom read : a range query returns a different number of rows because another transaction inserted or deleted rows that match the condition.

Isolation Levels – Causes of the Problems

Improper isolation leads to the issues above. MySQL provides four isolation levels:

Read Uncommitted (RU) : changes are visible before commit.

Read Committed (RC) : changes become visible only after commit.

Repeatable Read (RR) : data seen during a transaction remains consistent for the whole transaction.

Serializable (S) : full locking of rows ensures strict ordering.

Example values for V1, V2, V3 under each level illustrate how reads differ.

MVCC Principle

Multi‑Version Concurrency Control (MVCC) is used by InnoDB to handle read/write conflicts without locking, effective only under RC and RR isolation levels.

MVCC is implemented in the InnoDB engine. It relies on version chains and consistency views.

1. Version Chain

Each row stores hidden fields:

trx_id : the ID of the transaction that modified the row.

roll_pointer : a pointer to the previous version.

row_id (optional): present when a primary key or non‑null unique key exists.

When a row is updated, a new undo log entry is created and linked via roll_pointer, forming a chain like C → B → A → original.

2. Consistency View (ReadView)

ReadView determines which version of a row is visible to a transaction. It contains four key attributes:

m_ids : list of active transaction IDs at the moment of view creation.

min_trx_id : smallest ID in m_ids.

max_trx_id : largest ID in m_ids plus one.

creator_trx_id : the ID of the transaction that created the view (0 if none).

A row version is visible if:

trx_id equals creator_trx_id (own changes).

trx_id < min_trx_id (committed before view).

trx_id ≥ max_trx_id (created after view – invisible).

If min_trx_id ≤ trx_id < max_trx_id, visibility depends on whether trx_id is in m_ids (still active → invisible) or not (committed → visible).

Note: RC generates a new ReadView for each SELECT, while RR generates it only on the first SELECT.

RC and MVCC Example

Using a series of transactions (100, 200, 300, 400) and timestamps T1‑T10, the article shows how RC creates a fresh ReadView each read, resulting in the following observed values:

T4 (transaction 300 reads) → sees original value “小杰”. T7 (transaction 400 reads) → sees “B”. T10 (transaction 300 reads) → sees “E”.

RR and MVCC Example

Under RR, the same transaction reuses the ReadView generated at its first read, leading to:

T4 (transaction 300 reads) → “小杰”. T7 (transaction 400 reads) → “B”. T10 (transaction 300 reads again) → “D”.

The diagrams illustrate version chains and how the visibility rules are applied in each case.