MySQL 死锁案例分析（1）插入意向锁-低调大师

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

2024-12-02 214

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）想要对这条记录加插入意向锁，也需要等待。

事务 1（250489）和事务 2（250490）相互等待，就形成了死锁，过程如下：

事务 1 持有锁。
事务 2 等待获得事务 1 持有的锁。
事务 1 等待事务 2 获得并释放锁之后，才能获得插入意向锁。

4. 总结

如果事务 1 已经对某条记录加了排他 Next-Key 锁：

没有其它事务在等待获得这条记录的锁，事务 1 想要往这条记录前面的间隙插入记录，不需要等待获得插入意向锁，可以直接插入记录。
其它事务在等待获得这条记录的锁，事务 1 想要往这条记录前面的间隙插入记录，需要等待其它事务获得并释放锁之后，事务 1 才能获得插入意向锁，然后才能往这个间隙插入记录。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle</i1></i1></i1></i1></i1></i1></i1></i1></i1></i1></i1></i1></i1></id></i1></id></i1>

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/16580384

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

经济下行，利润却翻倍！AI救了这些企业的命

大家好，我是陈哥，今天想和大家聊聊企业落地AI~ 自2022年底ChatGPT问世以来，AI热度高居不下，这场科技革命正以不可阻挡之势改变着世界。 SpaceX和特斯拉的董事会成员史蒂夫·贾维森曾说：“机器学习令我们能够构建超越人类理解的软件解决方案，还能向我们展示人工智能如何为每个行业提供支持。” 近两年，随着AI技术的不断突破，AI已不仅仅只是技术工具，更是企业战略的重要组成部分。然而，企业想要落地AI并不是一蹴而就，也有一些读者朋友给我留言：尽管看到了AI的巨大潜力，但想要将其有效地融入到企业战略时，还是会感到无从下手。那么，本篇文章我将从企业落地AI的原因、做法、必要性三个方面展开谈谈，建议对企业落地应用AI感兴趣的朋友收藏阅读。一、商业竞争的本质是效率之争目前，不少企业已经顺利迈入大模型时代，借助AI的力量实现了转型升级，甚至在大环境不好的今天实现利润翻倍。然而，仍有部分企业在在AI应用的道路上步履维艰，对AI存在着怀疑、抵触、盲目引进等问题。现代管理学之父彼得·德鲁克曾说：“管理的核心在于做正确的事情，而不是仅仅把事情做正确。”因此，企业需要深刻理解AI的价值，才...

2024-12-02

211

摘要：本文介绍了BTree索引和UBTree索引的存储结构BlinkTree，分析它们相比传统B+树在读写场景、写写场景有更强的并发能力的原因。本文分享自华为云社区《【GaussTech技术专栏】GaussDB的BTree索引和UBTree索引》，作者：GaussDB 数据库。 1. 简介数据库通常使用索引来提高业务查询的速度。本文将深入介绍GaussDB中最常用的两种索引：BTree索引和UBTree索引。我们将重点解读BTree索引和UBTree索引的存储结构，探讨它们在读写并发、写写并发以及MVCC（多版本并发控制）能力方面的优势，并展望它们的未来演进。 2. BTree索引和UBTree索引结构 GaussDB的主流存储引擎有两种：Append Update存储引擎（Astore）和In-place Update存储引擎（Ustore）。更多Ustore，请阅《 GaussDB Ustore存储引擎解读》。在Astore中，索引默认采用BTree；在Ustore中，索引默认采用UBTree。相比于BTree，UBTree在叶子节点层额外维护了数据的MVCC信息。不...

2024-12-02

287

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。