Understanding MySQL 8.0 Deadlock Logs and Reproducing a Deadlock Scenario

Author: Hu Chengqing, DBA team member at iKexing, specializes in fault analysis and performance optimization.

Important Improvements

MySQL 8.0 deadlock logs now display the lock information held by transaction 1, which was missing in MySQL 5.7 and often criticized.

In contrast, MySQL 5.7 does not provide this detail.

Precautions

Sometimes the same lock appears both as held and waiting, which can cause confusion but is not a bug.

Why does this happen? Is it a bug?

It is not a bug; the following steps reproduce the deadlock scenario.

##设置RC隔离级别
CREATE TABLE `t2` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2` (`c2`)
);
insert into t2 values(1,1),(5,4),(20,20);

Deadlock logic:

Session 2 inserts successfully and acquires an X lock on the record with c2=10 (shown as lock_mode X locks rec but not gap in the log).

Session 1 attempts to insert a duplicate on c2=10, causing a unique‑key conflict; it needs an S lock with a gap (range (4,10]) which conflicts with Session 2’s X lock, so it waits (log shows lock mode S waiting).

Session 2 then tries to insert 9, which falls into the gap (4,10]; the intention lock is blocked by Session 1’s gap lock, logged as lock_mode X locks gap before rec insert intention waiting. Both sessions wait on each other, forming a deadlock.

In MySQL 8.0 the log shows that Session 1 both holds the S lock and is waiting for it, clarifying the situation.

Recommended Articles:

Technical Share | Zabbix Monitoring TiDB (Part 2)

Technical Share | Zabbix Monitoring TiDB (Part 1)

Technical Share | Meaning of "Pages flushed up to" in SHOW ENGINE INNODB STATUS

Community updates and additional resources are available via the links above.