36 | 记一次线上SQL死锁事故：如何避免死锁？

刘超



该思维导图由 AI 生成，仅供参考

你好，我是刘超。今天我们来聊聊死锁，开始之前，先分享个小故事，相信你可能遇到过，或能从中获得一点启发。
之前我参与过一个项目，在项目初期，我们是没有将读写表分离的，而是基于一个主库完成读写操作。在业务量逐渐增大的时候，我们偶尔会收到系统的异常报警信息，DBA 通知我们数据库出现了死锁异常。
按理说业务开始是比较简单的，就是新增订单、修改订单、查询订单等操作，那为什么会出现死锁呢？经过日志分析，我们发现是作为幂等性校验的一张表经常出现死锁异常。我们和 DBA 讨论之后，初步怀疑是索引导致的死锁问题。后来我们在开发环境中模拟了相关操作，果然重现了该死锁异常。
接下来我们就通过实战来重现下该业务死锁异常。首先，创建一张订单记录表，该表主要用于校验订单重复创建：
CREATE TABLE `order_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` int(11) DEFAULT NULL,
  `status` int(4) DEFAULT NULL,
  `create_date` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
) ENGINE = InnoDB
为了能重现该问题，我们先将事务设置为手动提交。这里要注意一下，MySQL 数据库和 Oracle 提交事务不太一样，MySQL 数据库默认情况下是自动提交事务，我们可以通过以下命令行查看自动提交事务是否开启：
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)
下面就操作吧，先将 MySQL 数据库的事务提交设置为手动提交，通过以下命令行可以关闭自动提交事务：
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

SQL死锁是数据库中常见的问题，本文通过分享一个实际项目中的死锁案例，详细介绍了死锁产生的原因和解决方法。作者首先描述了项目初期未将读写表分离导致的死锁异常，随后通过实战演示了如何重现死锁异常，并解释了死锁产生的原因。文章提到了避免死锁的措施，包括设置超时时间、使用唯一索引列、使用其他方式实现幂等性校验等。此外，还介绍了其他常见的SQL死锁问题，并提出了预防死锁的常规手段。最后，作者提出了一个思考题，鼓励读者分享解决类似问题的方法。整篇文章通俗易懂，通过实例详细解释了死锁问题的产生和解决方法，对于数据库开发人员具有一定的参考价值。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《Java 性能调优实战》，新⼈⾸单¥59

立即购买

登录后留言

全部留言(43)

最新
精选

张学磊
MySQL默认开启了死锁检测机制，当检测到死锁后会选择一个最小(锁定资源最少得事务)的事务进行回滚
作者回复: 这个回答是我想要的。 Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通过innodb_deadlock_detect = on 打开死锁检测。
2019-08-13

64
ty_young
老师您好，请问插入意向锁是一种表锁么
作者回复: 是的，共享锁和排他锁是属于行锁，意向锁都属于表锁
2020-04-12
4
11
ok
老师，请问事例中insert order_record的事务AB中，请解答下疑惑，我描述如下 1、事务A执行select 4 for update获取（4,+∞）间隙锁 2、图中B事务再执行select 5 for update获取（5,+∞）的间隙锁 3、事务A执行insert 4 发现事务A自己持有（4,+∞）间隙锁，所以不用等待呀！ 4、事务B执行insert 5 发现事务A没有commit，持有（4,+∞）间隙锁，所以等待事务A释放锁 5、事务A提交，事务B insert 5获取到锁，commit 请指出问题…
作者回复: 在3的insert操作中，回去获取插入意向锁，而插入意向锁也是一种gap锁，根据矩阵图，插入意向锁和gap间隙锁是冲突的，所以insert操作需要等待间隙锁的释放。
2019-08-16
6
11
我已经设置了昵称
老师。我们一般不会在查询的时候加上for update，我们的组长让我们事务中不要放查询语句，只能放插入或者更新，就是提前查好，组装好，然后开始执行事务。我觉得这其实会出现重复插入（并发量一高就会出现）。请问老师事务中真的不能做查询操作吗，还有查询的时候怎么防止同时两个事务查不到相对应的数据而造成重复插入
作者回复: for update是一种悲观锁实现，我们可以使用性能更好的乐观锁来实现，通过版本号来实现数据更新不丢失问题，这种方式是最佳选择。而对于插入时防重复问题，可以对不允许重复字段设置唯一索引，进行唯一约束，这是一种不友好的实现方式。
2019-08-13
3
7
阿杜
老师，有两个人闻到这个问题，感觉回答的我也不是很明白： 1:老师你最后放的那张图，为啥主健索引还需要获取非主键索引的锁啊，主键索引不是已经持有这一整行数据了么？ 2.老师，您最后的那个例子，更新status时要获取index_order_status非聚簇索引，这句话能稍微解释一下吗？谢谢了麻烦老师详细解答下。
作者回复: 1、因为更新索引字段要获取该字段的索引； 2、非聚簇索引就是非主键索引，即status字段索引。
2020-01-10
3
4
insist
事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁 ------------------ 老师，请问一下，1、为什么A、B可以同时持有gap锁呢？2、为什么获取意向锁之前需要等待对方的gap锁呢？比较迷茫
作者回复: 由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以事务A的select获取的是gap锁，事务B也是获取的gap锁，gap锁是相互兼容的，所以可以同时获取到。是为了防止幻读，需要通过插入意向锁实现阻塞等待gap锁的释放。
2019-12-26
3
3
月迷津渡
一个表它的主键是UUID生成的，如果说为了避免幻读而加了一个Next-key lock，那它会怎么锁的，感觉后插入的位置待定。。。还是全表锁？
作者回复: 因为主键是唯一索引，所以不会使用next-key lock
2019-10-21

1
达达队长
老师这一句不懂：事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁？应该是：A是(4,+∞）B是(5,+∞）吧
作者回复: 是的
2020-04-14


郭奉孝
老师，为什么订单表校验重复订单不在主表而要用这么一张冗余表
作者回复: 减少主订单表的查询操作
2020-03-30


孫やさん
老师，您最后的那个例子，更新status时要获取index_order_status非聚簇索引，这句话能稍微解释一下吗？谢谢了
作者回复: 在更新status时，由于是根据条件order_no来更新状态的，所以获取的是index_order_status索引，index_order_status是一个联合索引，由于不是主键，所以是非聚簇索引。
2019-12-25
4


收起评论