InnoDB Transaction System, MVCC, and Locking Mechanisms – A Comprehensive Overview

InnoDB supports row‑level locking and implements four isolation levels. The article first reviews basic concepts such as the ACID properties and distinguishes database consistency from distributed consistency.

Isolation Levels

Four ANSI/ISO SQL‑92 isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) are described, together with InnoDB’s implementation details and the anomalies each level permits.

MVCC

InnoDB uses Multi‑Version Concurrency Control (MVCC) to achieve non‑locking reads. Snapshot reads retrieve a consistent version of data, while current reads lock the latest version. The article lists which SQL statements are snapshot reads and which are current reads.

Key MVCC Techniques

ROW record format (primary key, columns, DB_TRX_ID, DB_ROLL_PTR)

Relationship between ROW and undo log

ReadView evaluation using low and high watermarks

Lock Types

Locks are classified by granularity (table vs. row), compatibility (shared vs. exclusive), and implementation (intention locks, record locks, gap locks, next‑key locks, insert‑intention locks, AUTO‑INC locks). The article explains the purpose of each type and how they interact.

Critical Structures

Important structures include lock_t::type_mode (32‑bit lock mode), global lock manager lock_sys_t, lock object lock_t, row lock lock_rec_t, table lock lock_table_t, record identifier RecID, transaction lock manager trx_lock_t, transaction object trx_t, and global transaction manager trx_sys_t. Their fields and relationships are illustrated.

Lock Acquisition Process

The article details the current‑read lock mode selection, fast path ( lock_rec_lock_fast) and slow path ( lock_rec_lock_slow) logic, bitmap conflict checks, and waiting queue handling.

Semi‑Consistent Read

Semi‑consistent reads reduce lock conflicts for UPDATE statements under READ COMMITTED or when innodb_locks_unsafe_for_binlog is enabled, by reading the latest committed version of a locked row and re‑issuing the read if the WHERE clause still matches.

Insert Lock Flow

Insert‑intention locks ( LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION) prevent phantom reads. The article shows the lock compatibility matrix and the steps performed in lock_rec_insert_check_and_lock, including conflict detection, wait‑queue insertion, and page‑level transaction‑ID updates.

Implicit Locks

InnoDB employs implicit locks that are only materialized when a potential conflict exists, reducing lock overhead. The conversion from implicit to explicit locks is performed by lock_rec_convert_impl_to_expl, with special handling for primary and secondary indexes.

Concurrency Scenario

A detailed example demonstrates how INSERT and SELECT … FOR SHARE interact without causing phantom reads, thanks to latch‑level coordination and lock‑wait mechanisms.

Lock Wake‑up and Rollback

Lock wake‑up occurs via lock grant, dead‑lock detection, or timeout. The CATS (Contention Aware Transaction Schedule) algorithm prioritises waiting transactions based on the number of dependent waiters. Rollback can be full‑transaction or to a SAVEPOINT, with row locks retained to honour two‑phase locking.