Understanding InnoDB Locks: Types, Mechanisms, and Deadlock Prevention

InnoDB Lock Overview

InnoDB supports multiple lock granularities. The main lock types are LOCK_TABLE (table lock) and LOCK_REC (row lock). Typical lock modes include shared (read) locks and exclusive (write) locks, often abbreviated as S and X.

Lock Modes and Compatibility

Shared locks (also called read locks) allow concurrent reads, while exclusive locks block both reads and writes on the same resource. InnoDB also defines intention locks ( IS and IX) to coordinate table‑level and row‑level locking.

Table Locks

Table locks lock the entire table and come in shared ( READ) and exclusive ( WRITE) forms. They are used by DDL statements such as ALTER TABLE and can be acquired manually with LOCK TABLES. Table locks are only visible to InnoDB when autocommit=0 and innodb_table_lock=1. Manual table locks are released with UNLOCK TABLES. Example usage:

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

Because table locks lock a large granularity, they are discouraged in most production workloads.

Intention Locks

Intention locks are special table‑level locks that indicate the type of row lock a transaction intends to acquire. An intention shared lock ( IS) precedes a row shared lock, and an intention exclusive lock ( IX) precedes a row exclusive lock. Their compatibility matrix allows intention locks to coexist with each other while conflicting with incompatible table locks.

Auto‑Increment Lock

The auto‑increment lock protects the generation of sequential IDs. When a transaction inserts a row with an AUTO_INCREMENT column, it first acquires this lock, blocking other inserts until the lock is released. The behavior is controlled by innodb_autoinc_lock_mode (default 1 before MySQL 8.0, default 2 after).

Row Locks

InnoDB row locks include record locks, gap locks, next‑key (or “record‑plus‑gap”) locks, and insert‑intention locks. Record locks protect specific index entries, gap locks protect the space between index entries, and next‑key locks protect both the record and the preceding gap. Insert‑intention locks are a form of gap lock used before an INSERT to prevent phantom rows.

Deadlock Scenarios

Deadlocks typically arise with row locks. Example 1 shows two transactions each locking a different row for update and then trying to lock the other's row, forming a cycle. Example 2 demonstrates shared‑to‑exclusive lock upgrades causing a deadlock. Example 3 illustrates phantom‑read deadlocks when two sessions lock a non‑existent gap with SELECT ... FOR UPDATE and then both attempt to insert the same row. Example 4 shows three concurrent inserts on a unique key causing lock escalation and deadlock. Example 5 highlights lock order inversion (locking secondary index before primary key versus the opposite) as a deadlock source.

To diagnose deadlocks, run SHOW ENGINE INNODB STATUS and look for the latest deadlock report.

Best Practices to Reduce Deadlocks

Access tables and rows in a consistent order across the application.

Keep transactions short; large transactions hold locks longer.

Ensure appropriate indexes so queries use index scans instead of full‑table scans.

Avoid excessive use of FOR SHARE or FOR UPDATE unless necessary.

Acquire the required lock strength early in the transaction to avoid lock upgrades.

Internal Representation of Locks

InnoDB stores lock information in a 64‑bit type_mode field. The low 4 bits represent lock_mode (S, X, IS, IX, AUTO_INC, etc.), the next 4 bits represent lock_type (table vs. row), and the remaining bits encode record_lock_type (gap, record, next‑key, insert intention, etc.). All locks are represented by a single struct lock_t:

struct lock_t {
  trx_t*          trx;          // owning transaction
  UT_LIST_NODE_T(lock_t) trx_locks; // list of locks held by the transaction
  ulint           type_mode;    // combined lock type and mode
  hash_node_t     hash;         // node in the global lock hash table
  dict_index_t*   index;        // index for row locks
  union {
    lock_table_t  lock_table;   // table‑level lock details
    lock_rec_t    rec_lock;     // row‑level lock details
  } un_member;
};

Row‑level lock details are stored in struct lock_rec_t, which contains the tablespace ID, page number, and a bitmap indicating which records on the page are locked. All row locks are hashed by (tablespace, page) into lock_sys->rec_hash, allowing fast lookup of locks on a given page.

Conclusion

InnoDB’s lock system, together with MVCC, provides consistency and isolation for transactions. Understanding the various lock types, their compatibility, and how they interact with isolation levels is essential for diagnosing performance problems and preventing deadlocks in MySQL databases.