InnoDB MVCC: Concepts, Implementation, and Visibility Rules

This article provides an in-depth explanation of InnoDB's Multi-Version Concurrency Control (MVCC) mechanism, which enables high-concurrency reads and writes by maintaining multiple versions of data rows.

It describes the core components: the undo log that stores old versions of rows, the readview structure used for visibility checks, and the three hidden columns (DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID) added to each row.

The visibility algorithm for READ COMMITTED and REPEATABLE READ isolation levels is detailed, showing how a row's transaction ID is compared against the readview's up_limit_id, low_limit_id, and the list of active transaction IDs to determine visibility.

Implementation steps for INSERT, UPDATE on clustered and secondary indexes are illustrated with code examples, such as:

INSERT INTO `user`(`id`,`name`,`score`) VALUES (9,'zhang3',60);

and

UPDATE `user` SET `score`=70 WHERE `id`=9;

The article also includes source code snippets showing how InnoDB adds system columns, updates index entries, assigns read views via trx_assign_read_view , and performs visibility checks with functions like read_view_sees_trx_id and lock_clust_rec_cons_read_sees .

The purge thread removes old undo log versions based on the oldest active readview, ensuring storage efficiency.

Finally, it explains the differences between RR and RC levels: in RR the readview is created at the first select and persists until transaction commit, while in RC a new readview is generated for each select.