Introduction to InnoDB Transaction Lock System

Preface

The article introduces InnoDB's transaction lock module, aiming to give readers a basic understanding of row‑level and table‑level locks, their internal implementations, and ends with two illustrative cases. All examples target MySQL 5.7.10.

Row‑level Locks

InnoDB supports several row‑level lock flags such as LOCK_REC_NOT_GAP (record‑only lock), LOCK_GAP (gap lock), LOCK_ORDINARY (next‑key lock), LOCK_S (shared lock), and LOCK_X (exclusive lock). Their usage varies with isolation levels (RC, RR) and query types (SELECT … FOR UPDATE, INSERT, foreign‑key checks, etc.). The article explains how each flag behaves, when GAP locks appear, and how to avoid them by switching isolation levels or enabling innodb_locks_unsafe_for_binlog.

Table‑level Locks

InnoDB defines five table‑level lock modes: LOCK_IS, LOCK_IX, LOCK_S, LOCK_X, and LOCK_AUTO_INC. Their compatibility follows the same matrix used for record locks. The text describes typical scenarios for each mode, including DDL phases, explicit LOCK TABLE statements, and auto‑increment handling with the innodb_autoinc_lock_mode variable.

Transaction Lock Management

All lock objects are attached to the global lock_sys and to individual transaction and table structures. The article outlines the steps for acquiring table‑level locks (checking existing locks, conflict detection, waiting, deadlock resolution) and row‑level locks (fast‑path via lock_rec_lock_fast, slow‑path via lock_rec_lock_slow, conflict checks, wait‑queue insertion). It also covers deadlock detection using a wait‑for graph, victim selection based on transaction weight, and lock release procedures, including special cases for AUTO‑INC locks and RC‑level DML.

Two Interesting Cases

Two deadlock examples are presented: (1) concurrent inserts on a primary‑key table causing lock‑upgrade deadlocks, and (2) RR‑level inserts that generate conflicts between non‑insert‑intention GAP X locks and insert‑intention X locks. SQL statements and diagrams illustrate the lock interactions.