Introduction to InnoDB Transaction Lock System

Preface

The purpose of this article is to give a brief introduction to InnoDB's transaction lock module, helping readers gain an initial understanding of row‑level and table‑level locks, their internal implementations, and concluding with two interesting case studies. All code examples are based on MySQL 5.7.10.

Row‑level Locks

InnoDB supports fine‑grained row‑level concurrency control. The most common lock types are:

LOCK_REC_NOT_GAP – locks only the record itself, not the preceding gap. It is the default lock for RC isolation level (except for duplicate‑key checks on unique secondary indexes, which use LOCK_ORDINARY).

LOCK_GAP – locks only the gap between records (or before the first/after the last record). Typically used in RR isolation level.

LOCK_ORDINARY (Next‑Key Lock) – locks the record and the preceding gap, solving phantom reads in RR isolation.

LOCK_S (Shared Lock) – used when a transaction reads a row and does not want it modified by others. Its behavior varies with isolation level and query type (e.g., SELECT ... IN SHARE MODE).

LOCK_X (Exclusive Lock) – prevents concurrent modifications of the same record, used by UPDATE, DELETE, and SELECT ... FOR UPDATE statements.

LOCK_INSERT_INTENTION – a type of GAP lock that allows multiple sessions to insert into the same gap without waiting for each other.

Examples of lock acquisition for foreign‑key checks, INSERT…SELECT, and duplicate‑key handling are illustrated with SQL statements such as:

create table t1 (a int, b int, primary key(a));
create table t2 (a int, b int, primary key(a), key(b), foreign key(b) references t1(a));
insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,6), (7,8), (10,11);
insert into t2 values (1,2), (2,2), (4,4);
delete from t1 where a = 10;

The article explains which locks are taken on the parent and child tables during such deletions.

Table‑level Locks

InnoDB defines five table‑level lock modes: LOCK_IS, LOCK_IX, LOCK_X, LOCK_S, and LOCK_AUTO_INC. Their compatibility follows the lock_compatibility_matrix. Since MySQL 5.5, MDL locks cover most table‑level needs, but InnoDB still uses its own locks for DDL, autoincrement handling, and certain internal operations.

Examples of when LOCK_X is taken include the final stage of a DDL operation, explicit LOCK TABLE ... WRITE, and discard/import of tablespaces. LOCK_S is used for read‑only table locks during DDL preparation or when autocommit is off.

The autoincrement lock behavior is controlled by the innodb_autoinc_lock_mode variable, with three modes: AUTOINC_OLD_STYLE_LOCKING (0), AUTOINC_NEW_STYLE_LOCKING (1), and AUTOINC_NO_LOCKING (2). The article shows how each mode affects concurrency and replication safety.

Transaction Lock Management

All transaction lock objects are attached to the global lock_sys. Row‑level locks are acquired via lock_rec_lock, which first attempts a fast‑path lock and falls back to a slow‑path algorithm when conflicts are detected.

If a conflict occurs, the engine creates a waiting lock object, performs deadlock detection using a wait‑for graph, and may select a victim transaction to roll back. The article details the steps for lock enqueueing, waiting, and wake‑up, including the handling of AUTO‑INC locks and the release of locks at transaction commit.

Two Interesting Deadlock Cases

Case 1: Concurrent inserts into a table with a primary key cause a deadlock because INSERT‑INTENTION X locks conflict with S locks held by other sessions.

Case 2: In RR isolation, a GAP X lock and an INSERT‑INTENTION X lock on the same gap conflict, leading to a deadlock as illustrated by the following schema and data:

create table t1 (a int primary key ,b int);
insert into t1 values (1,2),(2,3),(3,4),(11,22);

Both cases demonstrate how lock ordering and lock type interactions can produce deadlocks, and the article suggests design‑level strategies to avoid them.

Conclusion

Understanding the various InnoDB lock types, their compatibility matrix, and the internal lock management flow is essential for designing high‑concurrency applications and for diagnosing performance issues such as deadlocks and lock wait timeouts.