Understanding MySQL InnoDB MVCC and Transaction Isolation Levels

Inspired by a desire to explain complex concepts in plain language, this article demystifies the MVCC (Multi-Version Concurrency Control) mechanism used by MySQL's InnoDB storage engine.

It first reviews the four standard isolation levels—read uncommitted, read committed, repeatable read, and serializable—explaining how higher isolation improves data consistency at the cost of performance.

In InnoDB, every row update creates an undo log entry, forming a version chain that allows the engine to present different row versions to different transactions. The current value of a row is the latest version, but each transaction sees a snapshot based on its isolation level.

InnoDB rows contain two hidden columns: trx_id , the transaction ID that performed the change, and roll_point , a pointer to the previous version stored in the undo log.

When a transaction starts, InnoDB builds a view (snapshot) composed of four elements: m_ids (IDs of active (uncommitted) transactions), min_trx_id (smallest active ID), max_trx_id (next ID to be assigned), and creator_trx_id (the ID of the transaction creating the view). Visibility of a row version follows four rules based on comparisons between the version’s trx_id and these view parameters.

To illustrate, the article provides a sample table and data:

create table `user` (
`id` bigint not null,
`name` varchar(50) default null,
PROMARY KEY (`id`)
) ENGINE = InnoDB;
insert into user(id,name) values (1,'A');

Using this setup, it walks through a repeatable‑read scenario where transaction A performs three SELECTs while other transactions B and C modify the row. The analysis shows that A sees values A, A, and A for V1, V2, and V3 respectively, because the same snapshot is reused for all reads.

The article then repeats the analysis under the read‑committed level, where a new snapshot is created for each SELECT. In this case, A observes values A, B, and C for V1, V2, and V3, reflecting the most recent committed changes at each point.

Finally, the piece summarizes the benefits of MVCC: it enables concurrent reads and writes without locking, prevents dirty reads, and, in repeatable‑read mode, eliminates non‑repeatable reads by reusing a consistent snapshot.