MySQL InnoDB Deadlock Analysis and Resolution Guide

1. Problem Background

The author describes a typical interview scenario where candidates are asked about MySQL deadlocks, then transitions to a real production deadlock encountered during daily log cleanup.

2. Online Issue

Log excerpts show a deadlock occurring during INSERT operations on a checkout_detail table. The business logic updates historical records to invalid before inserting a new record for the same recycle order ID.

2.1 Online Exception Log

The error log indicates a deadlock and an insertion exception, with a screenshot of the relevant log lines.

2.2 Data Preparation

A test schema is created on MySQL 5.7.21 (production) and MySQL 8.0.32 (test). The table definition and sample data are prepared as follows:

CREATE TABLE `checkout_detail` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `recycle_order_id` bigint(20) NOT NULL COMMENT '回收单ID',
  `confirm_recycle_time` datetime NOT NULL COMMENT '确认回收时间',
  `contrast_type` int(4) NOT NULL COMMENT '对比类型：1:售前、2:后验、3:售后',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_recycle_order_id_contrast_type` (`recycle_order_id`,`contrast_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后验详情表';

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES
(1, 1, '2024-07-15 19:56:01', 1, "回收单1"),
(2, 10, '2024-07-15 19:56:01', 2, "回收单10"),
(3, 20, '2024-07-15 19:56:01', 3, "回收单20");

2.3 Problem Reproduction

2.3.1 Execution Flow

Step

Transaction A

Transaction B

Start

START TRANSACTION;

START TRANSACTION;

1

update checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 30;

2

update checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 40;

3

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES (30,30,'2024-07-15 19:56:01',1,"插入回收单30");

4

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES (40,40,'2024-07-15 19:56:01',1,"插入回收单40");

The author asks readers to consider which locks are taken and whether a deadlock occurs.

2.3.2 Deadlock Detection

Step 3 experiences lock wait, and step 4 results in a deadlock. The following command is used to view InnoDB status:

SHOW ENGINE INNODB STATUS;

The output (truncated) shows the latest detected deadlock with screenshots.

2.3.3 Deadlock Log Analysis

Details of Transaction 23087 and 23088 are examined, showing each transaction holding an X lock on the unique index uniq_idx_recycle_order_id_contrast_type and waiting for an insert‑intention lock on the same index's supremum record.

*** (1) "TRANSACTION":
TRANSACTION 23087, ACTIVE 22 sec inserting
... (details omitted) ...
*** (1) HOLDS THE "LOCK(S)":
RECORD LOCKS ... index uniq_idx_recycle_order_id_contrast_type ... lock_mode X
... (details omitted) ...
*** (1) WAITING FOR THIS LOCK TO BE "GRANTED":
RECORD LOCKS ... lock_mode X insert intention waiting

Similar blocks are provided for Transaction 23088.

3. Root Cause Analysis

The article references MySQL documentation on gap locks and insert‑intention locks, explaining that gap locks protect index gaps from concurrent inserts, while insert‑intention locks allow multiple transactions to queue for the same gap but become mutually exclusive when the gap is already locked.

Illustrative diagrams (images) show how InnoDB locks index records and the hidden supremum record.

4. Solution

When a deadlock is observed, first identify the statements that waited for locks.

Map those statements back to the business code (or logs) to locate the offending code path.

Analyze the code flow, paying attention to whether the data exists; the lock mode differs for existing vs. non‑existing rows.

In this case, the fix is to check for existence first: if the record exists, perform an UPDATE; otherwise, perform an INSERT.

5. Summary

Even when two transactions generate identical gap‑lock ranges, they do not conflict because gap locks are compatible; they only block other inserts.

When an INSERT hits a gap locked by another transaction, it creates an insert‑intention lock, which is mutually exclusive with the existing gap lock, leading to a deadlock.

About the author Huang Peizu, Java Development Engineer at Caihuoxia