Introduction to InnoDB Transaction Lock System in MySQL

Introduction

The purpose of this article is to give a concise introduction to InnoDB's transaction lock module, helping readers gain an initial understanding of its concepts, internal mechanisms, and practical usage.

Row‑level Locks

InnoDB supports row‑level concurrency control. The article describes several common lock types:

LOCK_REC_NOT_GAP – locks only the record itself without the preceding gap. In RC isolation this is the default record lock.

LOCK_GAP – locks only the gap between index records, typically used in RR isolation.

LOCK_ORDINARY (Next‑Key Lock) – locks the record and its preceding gap to prevent phantom reads under RR isolation.

LOCK_S (Shared lock) – used for read operations that should not be modified by other transactions.

LOCK_X (Exclusive lock) – prevents concurrent modifications, used by UPDATE, DELETE, and SELECT … FOR UPDATE.

LOCK_INSERT_INTENTION – a special GAP lock that allows multiple sessions to insert into the same gap without conflict.

Example table creation and data setup:

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);

Sample lock acquisition during DELETE FROM t1 WHERE a = 10 demonstrates how InnoDB adds LOCKREC_NOT_GAP|LOCK_X on the target record and a LOCK_ORDINARY|LOCK_S on the supremum record of the child table.

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 lock_compatibility_matrix. Intent locks (IS/IX) signal future row‑level lock needs and do not conflict with each other, but they conflict with S/X locks.

Examples of when LOCK_X is taken include DDL commit phases, explicit LOCK TABLE … WRITE statements, and tablespace discard/import operations.

Lock Management

All transaction locks are attached to the global lock_sys object and to individual transaction and table structures. The article outlines the steps for acquiring table locks, acquiring row locks (fast‑path vs. slow‑path), waiting, deadlock detection, and lock release.

Key functions mentioned: lock_table_create, lock_rec_lock, lock_rec_lock_fast, lock_rec_lock_slow, RecLock::add_to_waitq, DeadlockChecker::check_and_resolve, and lock_trx_release_locks.

Deadlock Detection and Resolution

When a lock conflict is detected, InnoDB builds a wait‑for graph using trx_t::lock.wait_lock. If a cycle is found, a victim transaction is chosen based on weight (e.g., read‑only vs. write, number of undo entries). Errors DB_LOCK_WAIT and DB_DEADLOCK are returned to the upper layer.

Interesting Deadlock Cases

Two illustrative deadlock scenarios are presented:

1. Concurrent inserts into a primary‑key table where sessions acquire X locks on the target record and S locks on the gap, leading to a lock‑upgrade deadlock.

2. In RR isolation, two sessions select a non‑existent row FOR UPDATE (acquiring GAP X locks) and then attempt to insert into the same gap, causing a conflict between GAP X and INSERT‑INTENTION X locks.

Both cases show how lock mode incompatibility can produce deadlocks and how InnoDB resolves them.