MySQL InnoDB Deadlock Case Study and Resolution

This article presents a detailed analysis of a MySQL InnoDB deadlock scenario that occurs during concurrent updates on a table with a composite index. It is part of a series aimed at helping developers and DBAs understand and resolve deadlocks.

1. Introduction

Deadlocks are challenging technical problems that many DBAs and developers encounter. This case study examines a specific deadlock caused by concurrent UPDATE statements on a table with a composite index (c, d).

2. Case Description

2.1 Business Scenario

A migration moves data from table A to a new table B. The downstream service receives a list of task_id values and processes them concurrently, leading to deadlocks during updates.

Note: Because B is a new table, it may not contain corresponding task_id values.

2.2 Environment

MySQL 5.6.24 with transaction isolation level REPEATABLE READ (RR).

create table x (
  id int not null auto_increment,
  c int not null default 0,
  d int not null default 0,
  primary key(id),
  key idxcd(c,d)
);

insert into x(c,d) values
  (1,0),(3,0),(5,0),(7,0),(10,0),(12,0),(14,0),(16,0);

2.3 Test Data

An image (omitted) shows the test data inserted into the table.

2.4 Deadlock Log

2018-04-20 05:55:0x7f75cdfff700
*** (1) TRANSACTION:
TRANSACTION 2235, ACTIVE 161 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 1016626, OS thread handle 140143880890112, query id 4070003 127.0.1 root updating
update x set d=1 where c in (5,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80
index idxcd of table `test`.x trx id 2235
lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 2237, ACTIVE 36 sec updating or deleting, thread declared inside InnoDB 4997
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 1016629, OS thread handle 140143944005376, query id 4070021 127.0.1 root updating
update x set d=1 where c=7
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80
index idxcd of table `test`.x trx id 2237
lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

2.5 Analysis of the Deadlock Log

The analysis explains the lock acquisition process for INSERT and UPDATE statements in InnoDB, focusing on the following points:

INSERT first acquires a LOCK_INSERT_INTENTION for uniqueness checks.

If no unique‑key conflict occurs, the row is inserted and the transaction holds LOCK_X + LOCK_REC_NOT_GAP .

If a unique‑key conflict exists, a S Next‑key lock is requested, which may block other sessions.

The compatibility matrix shows that GAP and Next‑Key locks block INSERT‑INTENTION locks, while Record‑to‑Record locks conflict directly.

In the presented scenario, two sessions acquire conflicting GAP and Next‑Key locks on the same index range, leading to a circular wait:

Session 2 acquires a GAP X lock on the range (5,0)–(7,0).

Session 1 attempts to insert a new record (5,1) and is blocked by Session 2’s GAP lock.

Session 1 holds a Next‑Key lock that blocks Session 2 from inserting (7,1).

The two sessions wait for each other, causing a deadlock.

2.6 Solution

The simplest fix is to remove column d from the composite index idxcd(c,d), leaving a single‑column index idxc(c). This eliminates the GAP/Next‑Key conflict that blocks INSERT‑INTENTION locks.

3. Conclusion

The deadlock arises from concurrent UPDATEs that internally perform delete‑plus‑insert operations on a secondary index, causing GAP and Next‑Key locks to interfere. Understanding InnoDB’s lock modes and compatibility matrix is essential for diagnosing and preventing such deadlocks.

Key takeaways:

Solid knowledge of lock fundamentals is required.

Deadlock logs alone may not reveal the exact SQL; collaboration with developers and reproducing the scenario is necessary.

Additional reading links (original Chinese articles) are provided at the end of the source.