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

Problem Background

A sudden increase in business volume caused a rapid hiring surge and more transaction processing. During routine log‑clearing a MySQL deadlock was detected, prompting an investigation.

Online Issue

Abnormal Log

The error log shows a deadlock at line 2, an exception during data insertion at line 6, and the offending method at line 20.

Data Preparation

Test environment:

Production MySQL version: 5.7.21

Test MySQL version: 8.0.32

Isolation level: REPEATABLE‑READ Table definition:

CREATE TABLE `checkout_detail` (
  `id` bigint NOT NULL COMMENT 'Primary key',
  `recycle_order_id` bigint NOT NULL COMMENT 'Recycle order ID',
  `confirm_recycle_time` datetime NOT NULL COMMENT 'Confirm recycle time',
  `contrast_type` int NOT NULL COMMENT '1:pre‑sale, 2:post‑sale, 3:after‑sale',
  `remark` varchar(255) DEFAULT '' COMMENT 'Remark',
  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='After‑sale detail table';

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

Problem Reproduction

Execution Flow

START TRANSACTION; -- Transaction A

UPDATE checkout_detail SET remark='update status' WHERE recycle_order_id=30; -- Tx A step 1

START TRANSACTION; -- Transaction B

UPDATE checkout_detail SET remark='update status' WHERE recycle_order_id=40; -- Tx B step 2

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES (30,30,'2024-07-15 19:56:01',1,'Insert recycle 30'); -- Tx A step 3 (waits)

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES (40,40,'2024-07-15 19:56:01',1,'Insert recycle 40'); -- Tx B step 4 (deadlock)

Step 3 experiences a lock wait; step 4 triggers a deadlock.

Deadlock Investigation

Running SHOW ENGINE INNODB STATUS; displays the latest deadlock. A screenshot of the deadlock log is shown below:

Deadlock Log Analysis

Transaction 23087

TRANSACTION 23087, ACTIVE 22 sec inserting
LOCK WAIT 3 lock struct(s), 2 row lock(s)
INSERT INTO checkout_detail ... VALUES (30,30,...)

Transaction 23087 holds an exclusive lock on the unique index uniq_idx_recycle_order_id_contrast_type but only on the hidden supremum record (no concrete row locked). It is waiting for an insert‑intention lock.

Transaction 23088

TRANSACTION 23088, ACTIVE 14 sec inserting
LOCK WAIT 3 lock struct(s), 2 row lock(s)
INSERT INTO checkout_detail ... VALUES (40,40,...)

Transaction 23088 holds the same type of exclusive lock on the supremum record and also waits for an insert‑intention lock, creating a circular wait.

Analysis of the Cause

In InnoDB, gap locks protect index gaps, while insert‑intention locks allow concurrent inserts into the same gap. Both transactions attempt to insert rows that share the same unique‑index key range. Each transaction acquires a gap lock on the index’s supremum record and then requests an insert‑intention lock, which is mutually exclusive with the other transaction’s lock, resulting in a deadlock.

Solution

Identify the statements that caused the lock wait from the deadlock log.

Map those statements back to the business code to understand the execution path.

Replace the “update‑then‑insert” pattern with an upsert: INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO, or explicitly check for existence before inserting.

This eliminates competing insert‑intention locks because only one transaction will perform the insert when the row does not exist.

Conclusion

Gap locks on identical ranges are compatible; they do not conflict with each other.

During an INSERT, if the target record falls within another transaction’s gap lock, MySQL creates an insert‑intention lock, which is mutually exclusive with the existing gap lock, leading to a deadlock.