MySQL InnoDB Lock Types and Deadlock Analysis

In distributed systems a common approach is to implement a distributed lock using a database table with a unique index on lock_key and lock_biz. The lock is obtained by inserting or deleting a row, which provides a non‑re‑entrant lock.

Recently a deadlock alarm appeared. Log analysis showed that different unique‑index values were involved in the deadlock. The underlying table is defined as:

CREATE TABLE `test_lock` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `gmt_create` datetime NOT NULL COMMENT 'creation time',
  `gmt_modified` datetime NOT NULL COMMENT 'modification time',
  `lock_key` varchar(128) NOT NULL COMMENT 'lock name',
  `lock_context` varchar(512) DEFAULT NULL COMMENT 'lock context',
  `lock_biz` varchar(64) NOT NULL COMMENT 'lock type',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uk_lock_name` (`lock_key`,`lock_biz`)
) ENGINE=InnoDB AUTO_INCREMENT=26229 DEFAULT CHARSET=utf8 COMMENT='distributed lock table';

The article then classifies MySQL lock scopes:

Global lock : locks the whole DB instance (e.g., FLUSH TABLES WITH READ LOCK); makes the DB read‑only and blocks DML/DDL.

Table lock : locks an entire table ( LOCK TABLES t READ|WRITE); shared vs exclusive.

Row lock : InnoDB row‑level lock, high concurrency but higher overhead and possible deadlocks.

Lock modes are summarized in a compatibility matrix (X vs S). The key point: exclusive (X) locks are incompatible with any other lock.

Further lock type classification:

Record lock : locks a single index record (e.g., SELECT id FROM t WHERE id=1 FOR UPDATE).

Gap lock : locks the interval between index records; prevents inserts into the gap.

Next‑key lock : combination of record lock and the preceding gap.

Insert intention lock : a special gap lock set before an INSERT to allow concurrent inserts into different gaps.

An illustrative deadlock scenario with four transactions (T1‑T4) is presented. The steps are:

T1 inserts a row with lock_key='140' and obtains an X record lock.

T2 tries to insert lock_key='140', hits a unique‑key conflict and acquires an S next‑key lock on the surrounding gap.

T3 inserts lock_key='144' and gets its own X record lock.

T4 also tries lock_key='144', receives an S next‑key lock.

Subsequent rollbacks and lock‑intention requests cause both T2 and T4 to wait for each other, resulting in a deadlock.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-15 20:34:19 0x70000ec62000
*** (1) TRANSACTION:
TRANSACTION 8501, ACTIVE 10 sec inserting
... insert into `test_lock`(..., '144', 'AccountUser')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 8495, ACTIVE 31 sec inserting
... insert into `test_lock`(..., '140', 'AccountUser')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock_mode X locks gap before rec insert intention waiting

The analysis shows that the deadlock originates from the combination of row locks, gap locks and insert‑intention locks. In practice, the problem was amplified by two design issues in the business layer:

High concurrency : many scheduled tasks executed the same settlement logic in parallel, leading to overlapping lock scopes.

Large transactions : a single transaction performed multiple table updates, enlarging the exclusive region and increasing lock contention.

Solutions applied:

Limit concurrency for small‑data settlement tasks to a single instance.

For large‑data tasks, change the sharding key from id to sellerId, aligning the lock granularity with business partitioning.

Separate lock/unlock operations into independent sub‑transactions to reduce the size of the critical section.

References to further reading on MySQL lock mechanisms and deadlock troubleshooting are listed at the end of the article.