How to Diagnose and Resolve MySQL InnoDB Deadlocks: A Step‑by‑Step Guide

Problem Background

The team noticed a sudden increase in business volume, leading to more hiring and higher load on the MySQL service. During routine log cleanup, a deadlock appeared in the error logs, prompting a detailed investigation.

Online Issue

The error log showed a deadlock and an exception during data insertion. The relevant business logic updates all historical recycle orders for a given recycle_order_id to an invalid state before inserting a new record.

Data Preparation

A test database was created with a checkout_detail table that mirrors the production schema. The table uses InnoDB, has a unique index on (recycle_order_id, contrast_type), and contains three sample rows.

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');

Problem Reproduction

Two concurrent transactions (A and B) perform the following steps:

START TRANSACTION; -- Transaction A
UPDATE checkout_detail SET remark='更新状态' WHERE recycle_order_id = 30;
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');

START TRANSACTION; -- Transaction B
UPDATE checkout_detail SET remark='更新状态' WHERE recycle_order_id = 40;
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');

During execution, step 3 (the first INSERT) experiences a lock wait, and step 4 (the second INSERT) results in a deadlock.

Deadlock Analysis

Running SHOW ENGINE INNODB STATUS; reveals the latest deadlock information. The log shows two transactions (23087 and 23088) both trying to insert rows with the same unique index uniq_idx_recycle_order_id_contrast_type. Each transaction holds an X lock on the index's “supremum” pseudo‑record and waits for an insert‑intention lock on the same index, causing a circular wait.

Key observations from the log:

Both transactions are in INSERT state.

Each holds a record lock on the unique index but on the hidden supremum record.

Both are waiting for an insert‑intention lock to be granted.

Root Cause

The deadlock arises because the unique index enforces a gap lock (or insert‑intention lock) on the range that includes the new recycle_order_id. When two transactions try to insert rows with different recycle_order_id values that fall into the same index gap, InnoDB creates competing insert‑intention locks, leading to a deadlock.

Gap locks prevent other transactions from inserting into the locked range, while insert‑intention locks are exclusive to each inserting transaction. When both transactions lock the same gap, they block each other.

Solution

To avoid the deadlock, modify the business logic to check for the existence of a record before inserting:

Query the table for the given recycle_order_id and contrast_type.

If a row exists, perform an UPDATE.

If no row exists, perform an INSERT.

This pattern eliminates the competing insert‑intention locks because only one transaction will attempt the INSERT for a given unique key.

Summary

Even when two transactions generate identical gap‑lock ranges, they do not conflict because gap locks are compatible; however, insert‑intention locks are mutually exclusive, which is why the deadlock occurred. Understanding InnoDB’s lock types—next‑key locks, gap locks, and insert‑intention locks—helps diagnose and prevent such issues.