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

1. Prepare Environment

Check the MySQL version (8.0.22) and the default isolation level (REPEATABLE‑READ). Disable autocommit to control transaction boundaries:

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22    |
+-----------+

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ       |
+-------------------------+

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1            |
+--------------+

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0            |
+--------------+

Create a table with a non‑unique index on age (the key point of the example) and insert two rows:

-- id is the auto‑increment primary key, age is a non‑unique index, name is a regular column
CREATE TABLE `user` (
  `id`   int NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `age`  int DEFAULT NULL COMMENT 'age',
  `name` varchar(255) DEFAULT NULL COMMENT 'name',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='user info table';

INSERT INTO `user` (age, name) VALUES (20, 'wangwu'), (10, 'zhaoliu');

2. Simulate a Real Deadlock

Open two terminals to run concurrent transactions:

Transaction A updates the row where age=20 (success).

Transaction B updates the row where age=10 (success).

Transaction A attempts to insert a row with age=15 and becomes blocked.

Transaction B inserts a row with age=30, which succeeds, and the blocked insert in Transaction A turns into a deadlock error.

-- Transaction A update
mysql> UPDATE user SET name='wangwu' WHERE age=20;
Query OK, 1 row affected (0.00 sec)

-- Transaction B update
mysql> UPDATE user SET name='zhaoliu' WHERE age=10;
Query OK, 1 row affected (0.00 sec)

-- Transaction A insert (blocked)
mysql> INSERT INTO user VALUES (NULL, 15, "tianqi");
-- (blocked)

-- Transaction B insert (succeeds)
mysql> INSERT INTO user VALUES (NULL, 30, "wangba");
Query OK, 1 row affected (0.00 sec)

After committing both transactions, the data shows that Transaction B’s changes are persisted while all of Transaction A’s changes have been rolled back.

3. Analyze the Deadlock Log

Run SHOW ENGINE INNODB STATUS to retrieve the latest deadlock information. The relevant part of the log looks like this (truncated for brevity):

LATEST DETECTED DEADLOCK
------------------------
2021-12-24 06:02:52 0x7ff7074f8700
*** (1) TRANSACTION:
TRANSACTION 2554368, ACTIVE 22 sec inserting
... LOCK WAIT 4 lock struct(s) ...
INSERT INTO user VALUES (NULL,15,"tianqi")
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... idx_age ... lock_mode X
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... idx_age ... lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 2554369, ACTIVE 14 sec inserting
... LOCK WAIT 5 lock struct(s) ...
INSERT INTO user VALUES (NULL,30,"wangba")
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... idx_age ... lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... idx_age ... lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Transaction A Log

Key points:

Holds an X lock on the record with age=20 (record lock + gap lock = next‑key lock covering (10,+∞)).

Waiting for an insert‑intention lock on the gap (10,20) needed by its own INSERT statement.

Transaction B Log

Key points:

Holds an X lock on the record with age=10 (next‑key lock covering (-∞,20)).

Waiting for an insert‑intention lock on the same gap (10,20) required by its INSERT of age=30.

4. Summarize the Cause

Both transactions acquire overlapping gap locks on the range (10,20). Each transaction then tries to insert a new row that requires an insert‑intention lock on that same gap, resulting in a circular wait. MySQL resolves the deadlock by rolling back the transaction with the smaller undo log (Transaction A).

5. Prevent Deadlocks in Practice

Access tables (or rows) in a consistent order across all applications.

Before updating, check that the target row exists; avoid unnecessary gap locks caused by updating non‑existent rows.

Prefer primary‑key updates whenever possible, as they generate only record locks.

Keep transactions short to reduce the window for lock contention.

When the workload tolerates it, use the READ COMMITTED isolation level to avoid gap locks introduced by REPEATABLE‑READ.

Understanding the types of locks (record, gap, next‑key, and insert‑intention) and how they interact is essential for diagnosing and eliminating deadlocks in MySQL.