Comprehensive Overview of the InnoDB Lock System in MySQL

MySQL's InnoDB storage engine implements a rich lock system that works together with MVCC to guarantee transaction consistency and isolation. Locks exist at multiple granularity levels: table locks (LOCK_TABLE) and row locks (LOCK_REC), each of which can be shared (S) or exclusive (X). Shared locks, also called read locks, allow concurrent reads, while exclusive locks block both reads and writes on the same resource.

In addition to the basic lock types, InnoDB defines intention locks (IS and IX) that are placed on tables before acquiring row‑level locks. These intention locks enable the engine to quickly determine whether a table‑level lock conflicts with any row‑level lock without scanning all rows. Special table‑level locks also exist, such as the auto‑increment lock, which serialises generation of AUTO_INCREMENT values, and the insert‑intention lock, a variant of gap locks used to prevent phantom reads during inserts.

Table locks are acquired automatically for certain DDL statements (e.g., ALTER TABLE) or can be requested explicitly with LOCK TABLES … READ|WRITE and released with UNLOCK TABLES. When using explicit table locks, the session must be in autocommit=0; otherwise the lock is released at the end of the statement.

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ;
-- do something with t1 and t2
COMMIT;
UNLOCK TABLES;

Row locks come in several forms:

Record locks – lock a specific index record.

Gap locks – lock the interval between index records (used to prevent phantom reads).

Next‑key locks – a combination of a record lock and the preceding gap (left‑open, right‑closed interval).

Insert‑intention locks – a special gap lock placed before an INSERT to reserve the gap.

Different SQL statements and isolation levels affect which lock type is taken. Under the default REPEATABLE READ level, InnoDB may acquire next‑key locks for non‑unique index scans, while READ COMMITTED disables gap locks, turning next‑key locks into pure record locks. SERIALIZABLE implicitly adds shared locks to all SELECTs, making reads block writes.

Deadlocks occur only with row‑level locks. InnoDB detects cycles using a depth‑first search; if the lock‑wait graph depth exceeds LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK (default 200), a deadlock is reported. The engine then rolls back the transaction with the smallest rollback cost. Typical deadlock patterns include:

Two transactions each lock a row for update and then try to lock the other's row.

One transaction acquires a shared lock, updates the row (requiring an exclusive lock), while another transaction does the same on the same row, forming a cycle.

Concurrent INSERT … FOR UPDATE on a non‑existent row creates gap locks that conflict with each other's insert‑intention locks.

Different lock acquisition orders on primary and secondary indexes (e.g., updating different columns in opposite order).

Example of a deadlock scenario (simplified):

CREATE TABLE student (
  id INT NOT NULL,
  uuid VARCHAR(64) NOT NULL,
  name VARCHAR(64) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uuid_index (uuid),
  KEY name_index (name)
) ENGINE=InnoDB;

Two sessions execute the following statements concurrently:

-- Session 1
BEGIN;
SELECT * FROM student WHERE id = 1 FOR UPDATE;
SELECT * FROM student WHERE id = 2 FOR UPDATE; -- blocks

-- Session 2
BEGIN;
SELECT * FROM student WHERE id = 2 FOR UPDATE;
SELECT * FROM student WHERE id = 1 FOR UPDATE; -- deadlock detected

InnoDB provides the SHOW ENGINE INNODB STATUS command to view the latest deadlock trace, which is useful for debugging.

The internal representation of a lock is a 64‑bit unsigned long where the low 4 bits store lock_mode, the next 4 bits store lock_type, and the remaining bits encode the record‑specific information. All locks are stored in a global hash table ( lock_sys->rec_hash) keyed by the page (space, page_no). Each bucket contains a linked list of lock_t structures; a single lock_t can represent multiple row locks on the same page using a bitmap, which saves memory.

Key take‑aways:

Use a consistent lock acquisition order across the application.

Keep transactions short to reduce lock holding time.

Prefer appropriate indexes to avoid full‑table row‑locking under REPEATABLE READ.

Avoid unnecessary FOR SHARE or FOR UPDATE clauses when a simple SELECT suffices.

Detect and handle deadlock errors by retrying the transaction.

Overall, understanding the variety of InnoDB lock types, their interaction with isolation levels, and the engine's internal lock structures is essential for diagnosing performance issues and preventing deadlocks in MySQL workloads.