您现在的位置是：首页 > 文章详情

MySQL 死锁案例分析（1）插入意向锁

日期：2024-12-02点击：111收藏

insert 语句导致的死锁案例分析。

> 本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。

正文

1. 准备工作

创建测试表：

CREATE TABLE `t_deadlock_1` ( `id` int NOT NULL AUTO_INCREMENT, `i1` int DEFAULT NULL, `i2` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_i1` (`i1`) ) ENGINE = InnoDB;

插入测试数据：

INSERT INTO `t_deadlock_1` (`id`, `i1`, `i2`) VALUE (22, 2, 3), (23, 5, 4), (24, 6, 7);

把事务隔离级别设置为 REPEATABLE-READ（如已设置，忽略此步骤）：

SET transaction_isolation = 'REPEATABLE-READ'; -- 确认设置成功 SHOW VARIABLES like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+

2. 加锁情况

创建 2 个 MySQL 连接，开启 2 个事务，执行以下 SQL：

-- session 1（事务 1） BEGIN; DELETE FROM t_deadlock_1 WHERE `i1` = 5; -- session 2（事务 2） BEGIN; DELETE FROM t_deadlock_1 WHERE `i1` = 5;

在 session 1 中执行以下 select 语句查看加锁情况：

select engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks where object_name = 't_deadlock_1' and lock_type = 'RECORD'\G ***************************[ 1. row ]*************************** engine_transaction_id | 250490 object_name | t_deadlock_1 index_name | idx_i1 lock_type | RECORD lock_mode | X lock_status | WAITING lock_data | 5, 23 ***************************[ 2. row ]*************************** engine_transaction_id | 250489 object_name | t_deadlock_1 index_name | idx_i1 lock_type | RECORD lock_mode | X lock_status | GRANTED lock_data | 5, 23 ***************************[ 3. row ]*************************** engine_transaction_id | 250489 object_name | t_deadlock_1 index_name | PRIMARY lock_type | RECORD lock_mode | X,REC_NOT_GAP lock_status | GRANTED lock_data | 23 ***************************[ 4. row ]*************************** engine_transaction_id | 250489 object_name | t_deadlock_1 index_name | idx_i1 lock_type | RECORD lock_mode | X,GAP lock_status | GRANTED lock_data | 6, 24

加锁情况第 2 ~ 4 条，是事务 1 的加锁情况。

事务 1 执行 delete 语句过程中，会先扫描需要删除的记录，并对扫描到的记录加锁。

扫描过程使用了二级索引 idx_i1，先定位到这个索引中 <i1 = 5, id="23"> 的记录，加排他 Next-Key 锁，对应加锁情况第 2 条（2. row）。

回表查询主键索引中 <id = 23> 的记录，加排他普通记录锁，对应加锁情况第 3 条（3. row）。

扫描到匹配 where 条件的第 1 条记录之后，接着扫描下一条记录，也就是二级索引 idx_i1 中 <i1 = 6, id="24"> 的记录，加排他间隙锁，对应加锁情况第 4 条（4. row）。

因为这条记录不匹配 where 条件，不需要回表查询对应的主键索引记录，所以没有对主键索引中 <id = 24> 的记录加锁。

按照 <i1 = 5, id="23"> 的记录加锁情况，<i1 = 6, id="24"> 的记录也应该加排他 Next-Key 锁，但实际上只加了排他间隙锁。

这是因为 InnoDB 对命中索引的等值查询条件做了特殊处理。

可重复读隔离级别默认会对扫描到的记录加排他 Next-Key 锁。如果 InnoDB 发现记录不匹配命中索引的等值查询条件，会改为对这条记录加排他间隙锁，避免锁定不匹配的记录本身，以缩小加锁范围。

加锁情况第 1 条（1. row），是事务 2 的加锁情况。

事务 2 执行 delete 语句过程中，也会先扫描需要删除的记录，并对扫描到的记录加锁。

扫描过程同样使用了二级索引 idx_i1，先定位到这个索引中 <i1 = 5, id="23"> 的记录，加排他 Next-Key 锁。

但是，因为事务 1 先对这条记录加了排他 Next-Key 锁，事务 2 的加锁操作被阻塞，进入锁等待状态。

介绍完事务 1 和事务 2 的加锁情况，我们再在 session 1 中执行以下 insert 语句，插入一条记录：

INSERT INTO t_deadlock_1 (`id`, `i1`, `i2`) VALUES (25, 2, 10);

结果就出现了死锁，事务 2 被选择成为死锁受害事务，回滚了：

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

3. 死锁分析

为了找到死锁原因，我们需要借助死锁日志，可以在 session 1 或者 session 2 中执行以下 show 语句，查看最新的死锁日志：

SHOW ENGINE InnoDB STATUS\G ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-09-07 07:48:49 0x7000087c0000 *** TRANSACTION: -- 事务 2 TRANSACTION 250490, ACTIVE 19 sec starting index read ... DELETE FROM t_deadlock_1 WHERE `i1` = 5 *** HOLDS THE LOCK(S): RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \ lock_mode X waiting Record lock, heap no 3 PHYSICAL RECORD: \ n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000017; asc ;; *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \ lock_mode X waiting Record lock, heap no 3 PHYSICAL RECORD: \ n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000017; asc ;; *** TRANSACTION: -- 事务 1 TRANSACTION 250489, ACTIVE 26 sec inserting ... INSERT INTO t_deadlock_1 (`id`, `i1`, `i2`) VALUES (25, 2, 10) *** HOLDS THE LOCK(S): RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \ lock_mode X Record lock, heap no 3 PHYSICAL RECORD: \ n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000017; asc ;; *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \ lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: \ n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000017; asc ;;

以上是从 SHOW ENGINE InnoDB STATUS 结果中摘出来的最新的死锁日志。

> 为了方便手机上阅读，我对格式做了一些调整，内容也有一点小小的修改，去掉了事务前面的编号。

从死锁日志可以看到，事务 1（250489）和事务 2（250490）加锁发生死锁，都是因为二级索引 idx_i1 中的一条记录：

/* i1 字段 */ 0: len 4; hex 80000005; asc ;; /* id 字段 */ 1: len 4; hex 80000017; asc ;;

在《30. 死锁日志详解》这篇文章中，我们介绍过把死锁日志中整数类型字段值转换为整数的方法。

我们用这个方法，把上面死锁日志中这条记录的两个字段值转换为整数：

## i1 字段，输出：5 echo $((0x80000005 ^ (1 &lt;&lt; (4 * 8 - 1)))) ## id 字段，输出：23 echo $((0x80000017 ^ (1 &lt;&lt; (4 * 8 - 1))))

从以上输出可以看到，事务 1（250489）和事务 2（250490）加锁发生死锁，都是因为二级索引 idx_i1 中 <i1 = 5, id="23"> 的记录。

*** TRANSACTION: -- 事务 1 TRANSACTION 250489, ACTIVE 26 sec inserting ... *** HOLDS THE LOCK(S): RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \ lock_mode X ... *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \ lock_mode X locks gap before rec insert intention waiting ...

上面是从死锁日志中摘出来的一小段，从这段日志可以看到，事务 1（250489）持有 <i1 = 5, id="23"> 的记录的排他 Next-Key 锁，等待获得这条记录的插入意向锁。

*** TRANSACTION: -- 事务 2 TRANSACTION 250490, ACTIVE 19 sec starting index read ... DELETE FROM t_deadlock_1 WHERE `i1` = 5 *** HOLDS THE LOCK(S): RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \ lock_mode X waiting ... *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 232 page no 5 n bits 72 \ index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \ lock_mode X waiting ...

上面也是从死锁日志中摘出来的一小段，从这段日志可以看到，事务 2（250490）的 HOLDS THE LOCK(S) 和 WAITING FOR THIS LOCK TO BE GRANTED 的记录都处于 waiting 状态。

这是因为事务 2（250490）在等待获得事务 1（250489）持有的 <i1 = 5, id="23"> 的记录的排他 Next-Key 锁，又阻塞了事务 1（250489）对 <i1 = 5, id="23"> 的记录加插入意向锁。

既然事务 1（250489）已经持有 <i1 = 5, id="23"> 的记录的排他 Next-Key 锁，也就是既锁定了这条记录，又锁定了它前面的间隙。

理论上来说，事务 1（250489）再对这条记录加插入意向锁，可以直接获得锁。

为什么会被事务 2（250490）阻塞呢？

如果事务 1（250489）因为持有这条记录的排他 Next-Key 锁，就可以直接获得这条记录的插入意向锁。

获得插入意向锁之后，插入 <i1 = 2, id="25"> 的记录到 <i1 = 5, id="23"> 的记录前面。

新插入的记录，会导致事务 1 和事务 2 原来对 <i1 = 5, id="23"> 的记录加的锁都需要拆分。

已经获得的锁，拆分是没有问题的。

事务 2（250490）在等待获得 <i1 = 5, id="23"> 的记录的排他 Next-Key 锁，也会拆分，得到两个处于等待状态的锁。

然而，InnoDB 却不允许一个事务同时有两个处于等待状态的锁。

基于这个规则，虽然事务 1（250489）已经持有 <i1 = 5, id="23"> 的记录的排他 Next-Key 锁，但是因为事务 2（250490）在等待获得这条记录的排他 Next-Key 锁，事务 1（250489）想要对这条记录加插入意向锁，也需要等待。