InnoDB Transaction Isolation Levels: Concepts, Implementation Details, and Practical Cases

MySQL uses multiple storage engines, and InnoDB provides the most complete transaction implementation, supporting all SQL standard isolation levels and employing MVCC for high‑concurrency reads. This article first introduces basic concepts such as ACID, isolation, and the four standard isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable), explaining why they exist to prevent dirty reads, non‑repeatable reads, and phantom reads.

It then details how InnoDB defines isolation levels in storage/innobase/include/trx0trx.h and how the server layer maps its own isolation definitions to InnoDB’s levels via the function innobase_map_isolation_level in storage/innobase/handler/ha_innodb.cc . The initialization of the isolation level is stored in the trx_t structure’s isolation_level field and is set in ha_innobase::store_lock .

Two practical cases are presented. Case 1 demonstrates phantom‑read prevention under Repeatable Read by creating a table, inserting rows, and using SELECT ... WHERE f2 IS NULL FOR UPDATE in one session while another session attempts inserts, showing which operations are blocked. Case 2 analyzes lock behavior by tracing functions such as RecLock::lock_add , lock_rec_set_nth_bit , and handler::read_range_next , revealing that under Repeatable Read and Serializable levels InnoDB may acquire extra row locks during index reads.

The article concludes that after setting the desired isolation level, InnoDB decides whether to use MVCC and gap locks, and that higher isolation levels increase resource consumption while preventing undesirable concurrency phenomena.