MySQL 8.0 InnoDB中并发事务为何引发死锁？

表结构与测试步骤 #

建表SQL #

CREATE DATABASE IF NOT EXISTS humans;
USE humans;

CREATE TABLE IF NOT EXISTS address (
    last_name VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    PRIMARY KEY (last_name)
);

INSERT INTO address values ("x", "abcd");
INSERT INTO address values ("y", "asdf");

CREATE TABLE IF NOT EXISTS names (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (first_name, last_name),
    FOREIGN KEY (last_name) REFERENCES address(last_name)
);

测试流程 #

1. 启动两个独立事务：
   • 事务1：

     START TRANSACTION;
     DELETE FROM names WHERE last_name = "x";
     -- 不提交/回滚
     

   • 事务2：

     START TRANSACTION;
     DELETE FROM names WHERE last_name = "y";
     -- 不提交/回滚
     

2. 继续执行插入：
   • 事务1执行：

     INSERT INTO names VALUES ("a", "x");
     

   • 事务2执行：

     INSERT INTO names VALUES ("b", "y");
     

此时触发死锁。

环境信息 #

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table names;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| names | CREATE TABLE `names` (
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

死锁日志（SHOW ENGINE INNODB STATUS输出） #

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-06-13 09:46:39 0x700005d8d000
*** (1) TRANSACTION:
TRANSACTION 23728, ACTIVE 305 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1177, OS thread handle 123145414819840, query id 307296 localhost root update
INSERT INTO names VALUES ("a", "x")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 23729, ACTIVE 302 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1178, OS thread handle 123145415884800, query id 307297 localhost root update
INSERT INTO names VALUES  ("b", "y")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS

疑问：InnoDB采用行锁机制，两个事务分别操作不同的last_name记录，为何会触发死锁？

1. 隐式索引的生成 #

names表定义了外键FOREIGN KEY (last_name) REFERENCES address(last_name)，InnoDB会自动为外键列last_name创建普通索引——这是锁冲突的核心载体。

2. DELETE操作的锁行为 #

由于测试时names表是空表，执行DELETE FROM names WHERE last_name = "x";时，没有匹配的行，InnoDB会在last_name索引上添加间隙锁，锁定从最小索引值到supremum记录（InnoDB为每个索引末尾添加的虚拟记录，代表比所有实际值都大的边界）之间的整个间隙。
同理，事务2的DELETE操作也会锁定到supremum的同一间隙——因为空表中，x和y都属于“小于supremum”的同一范围，不存在独立的行锁。

3. INSERT操作的循环等待 #

当事务1执行插入时，需要获取插入意向锁（一种特殊的间隙锁，标记插入意图），但此时事务2已持有到supremum的X锁，事务1进入等待；反过来，事务2的插入操作也需要获取插入意向锁，但事务1已持有到supremum的X锁，事务2也进入等待。
这种“互相持有对方需要的锁”的循环等待，直接触发了死锁。

• 调整事务隔离级别：将隔离级别改为READ COMMITTED，该级别下InnoDB会关闭普通索引的间隙锁（外键场景的间隙锁也会被弱化），避免空表DELETE时锁定过大范围。
• 优化操作逻辑：如果业务允许，可先插入数据再执行删除；或确保DELETE操作有匹配行（此时会加行锁而非间隙锁）。

内容的提问来源于stack exchange，提问作者Sanath Manavarte