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

Aug 13, 2019

36 | 记一次线上SQL死锁事故：如何避免死锁？-极客时间



下载APP





关闭

讲堂部落提薪训练营云原生训练营架构实战营企业版极客商城兑换中心 App下载浏览器插件

渠道合作

推荐作者

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

2019-08-13 刘超来自北京

《Java性能调优实战》

课程介绍



讲述：李良

时长09:31大小8.70M



你好，我是刘超。今天我们来聊聊死锁，开始之前，先分享个小故事，相信你可能遇到过，或能从中获得一点启发。
之前我参与过一个项目，在项目初期，我们是没有将读写表分离的，而是基于一个主库完成读写操作。在业务量逐渐增大的时候，我们偶尔会收到系统的异常报警信息，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)
订单在做幂等性校验时，先是通过订单号检查订单是否存在，如果不存在则新增订单记录。知道具体的逻辑之后，我们再来模拟创建产生死锁的运行 SQL 语句。首先，我们模拟新建两个订单，并按照以下顺序执行幂等性校验 SQL 语句（垂直方向代表执行的时间顺序）：
此时，我们会发现两个事务已经进入死锁状态。我们可以在 information_schema 数据库中查询到具体的死锁情况，如下图所示：
看到这，你可能会想，为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？试想下，如果是两个订单号一样的请求同时进来，就有可能出现幻读。也就是说，一开始事务 A 中的查询没有该订单号，后来事务 B 新增了一个该订单号的记录，此时事务 A 再新增一条该订单号记录，就会创建重复的订单记录。面对这种情况，我们可以使用锁间隙算法来防止幻读。
死锁是如何产生的？上面我们说到了锁间隙，在第 33 讲中，我已经讲过了并发事务中的锁机制以及行锁的具体实现算法，不妨回顾一下。
行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。record lock 是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁；next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。
只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock。
在 MySQL 中，gap lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。
当我们执行以下查询 SQL 时，由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）。
SELECT id FROM demo.order_record where order_no = 4 for update;
执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁。
以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。
INSERT INTO demo.order_record(order_no, status, create_date) VALUES (5, 1, ‘2019-07-13 10:57:03’);
我们可以通过以下锁的兼容矩阵图，来查看锁的兼容性：
避免死锁的措施知道了死锁问题源自哪儿，就可以找到合适的方法来避免它了。
避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，另一个事务就可以继续执行了。这种方法简单有效，在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的。
另外，我们还可以将 order_no 列设置为唯一索引列。虽然不能防止幻读，但我们可以利用它的唯一性来保证订单记录不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会抛出异常。
我们还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。
其它常见的 SQL 死锁问题这里再补充一些常见的 SQL 死锁问题，以便你遇到时也能知道其原因，从而顺利解决。
我们知道死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立。所以在一些经常需要使用互斥共用一些资源，且有可能循环等待的业务场景中，要特别注意死锁问题。
接下来，我们再来了解一个出现死锁的场景。
我们讲过，InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果我们之前使用辅助索引来更新数据库，就需要修改为使用聚簇索引来更新数据库。如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。
我们还是以上面的这个订单记录表来重现下聚簇索引和辅助索引更新时，循环等待锁资源导致的死锁问题：
出现死锁的步骤：
综上可知，在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。
总结数据库发生死锁的概率并不是很大，一旦遇到了，就一定要彻查具体原因，尽快找出解决方案，老实说，过程不简单。我们只有先对 MySQL 的 InnoDB 存储引擎有足够的了解，才能剖析出造成死锁的具体原因。
例如，以上我例举的两种发生死锁的场景，一个考验的是我们对锁算法的了解，另外一个考验则是我们对聚簇索引和辅助索引的熟悉程度。
解决死锁的最佳方式当然就是预防死锁的发生了，我们平时编程中，可以通过以下一些常规手段来预防死锁的发生：
1. 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
2. 在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
3. 更新表时，尽量使用主键更新；
4. 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
5. 设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
思考题除了设置  innodb_lock_wait_timeout  参数来避免已经产生死锁的 SQL 长时间等待，你还知道其它方法来解决类似问题吗？
期待在留言区看到你的见解。也欢迎你点击“请朋友读”，把今天的内容分享给身边的朋友，邀请他一起讨论。

分享给需要的人，Ta购买本课程，你将得18元

生成海报并分享

赞 9

提建议

35 | MySQL调优之索引：索引的失效与优化

37 | 什么时候需要分表分库？

 写留言

精选留言(41)

张学磊
2019-08-13
MySQL默认开启了死锁检测机制，当检测到死锁后会选择一个最小(锁定资源最少得事务)的事务进行回滚
作者回复: 这个回答是我想要的。 Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通过innodb_deadlock_detect = on 打开死锁检测。
63
ty_young
2020-04-12
老师您好，请问插入意向锁是一种表锁么
作者回复: 是的，共享锁和排他锁是属于行锁，意向锁都属于表锁
共 4 条评论
11
ok
2019-08-16
老师，请问事例中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操作需要等待间隙锁的释放。
共 6 条评论
10
我已经设置了昵称
2019-08-13
老师。我们一般不会在查询的时候加上for update，我们的组长让我们事务中不要放查询语句，只能放插入或者更新，就是提前查好，组装好，然后开始执行事务。我觉得这其实会出现重复插入（并发量一高就会出现）。请问老师事务中真的不能做查询操作吗，还有查询的时候怎么防止同时两个事务查不到相对应的数据而造成重复插入
作者回复: for update是一种悲观锁实现，我们可以使用性能更好的乐观锁来实现，通过版本号来实现数据更新不丢失问题，这种方式是最佳选择。而对于插入时防重复问题，可以对不允许重复字段设置唯一索引，进行唯一约束，这是一种不友好的实现方式。
共 3 条评论
7
阿杜
2020-01-10
老师，有两个人闻到这个问题，感觉回答的我也不是很明白： 1:老师你最后放的那张图，为啥主健索引还需要获取非主键索引的锁啊，主键索引不是已经持有这一整行数据了么？ 2.老师，您最后的那个例子，更新status时要获取index_order_status非聚簇索引，这句话能稍微解释一下吗？谢谢了麻烦老师详细解答下。
展开
作者回复: 1、因为更新索引字段要获取该字段的索引； 2、非聚簇索引就是非主键索引，即status字段索引。
共 3 条评论
4
a、
2019-08-13
1.设置Transaction的超时时间 2.设置Transaction的级别为串行化级别
4
insist
2019-12-26
事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁 ------------------ 老师，请问一下，1、为什么A、B可以同时持有gap锁呢？2、为什么获取意向锁之前需要等待对方的gap锁呢？比较迷茫
作者回复: 由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以事务A的select获取的是gap锁，事务B也是获取的gap锁，gap锁是相互兼容的，所以可以同时获取到。是为了防止幻读，需要通过插入意向锁实现阻塞等待gap锁的释放。
共 3 条评论
3
cky.宇
2019-11-17
感觉还可以把隔离级别改成RC。RR下锁比较严格，举两个例子：一个就是间隙锁的原因，就像老师的例子一样，如果一个事务在表t1 insert后没有commit，其他事务就不能对表t1进行insert，这样就可能会出现用户A的insert锁住了用户B的insert的情况，其实用户A和B是业务不相关的。而RC下没有间隙锁，不会有这种情况。第二个就是RR加锁的范围更大，RR下会锁住所有扫描过的行，只有commit后才会全部释放，例如：select no from orders where status = 1 and create_at > xx for update; 其中只有status有索引，那么mysql就要先扫描status索引再回表找满足create_at的行。如果是RR下，会锁住所有status=1的行，直到commit后释放。如果是RC下，当找到满足条件(status, craeted_at)的行后，会释放掉不满住条件但是status=1的行，不需要等到commit。这些细节都会造成RC和RR的性能差距很大。而一些需要重复读的需求可以通过代码来保证。
展开
4
月迷津渡
2019-10-21
一个表它的主键是UUID生成的，如果说为了避免幻读而加了一个Next-key lock，那它会怎么锁的，感觉后插入的位置待定。。。还是全表锁？
作者回复: 因为主键是唯一索引，所以不会使用next-key lock
1
一步
2021-10-16
gap lock 的范围不是根据数据库中已有的记录来确定的吗？
大明猩
2021-09-13
这种事务A,事务B同时执行时利用的多线程吗，还是单纯用的SQL语句
大明猩
2021-09-13
这种事务A，事务B的先后顺序是怎么模拟的不会搞啊
共 1 条评论
胖佳
2021-09-07
老师，能介绍一下mysql集群使用的ndbcluster引擎与innoDB在加锁方面的区别吗？
李亚方
2021-08-06
【作者回复：为了防止幻读，需要通过插入意向锁实现阻塞等待gap锁的释放。】RR隔离级别是允许出现幻读的呀？
VIC
2021-04-06
在一个方法里实现查询，插入。可以吗
皇家救星
2021-02-23
请问老师，平时正式环境mysql修改事务隔离级别的多吗。用rc级别跟rr级别比，会有什么缺点（优点是不是只有减少死锁）
刘宽
2020-10-22
你那个冲突的表格有错误，插入意向锁不影响间隙锁
torres
2020-09-21
事物A 应该是先获取order_no 的非聚簇索引，再获取主键索引吧？而不是 order_status 非聚簇索引，此处是笔误吗，还是我的理解问题？
黄平
2020-07-06
聚簇索引和辅助索引更新问题，是在可重复读的隔离级别下才有是吗？
ty_young
2020-06-06
老师您好，第二个使用非聚簇索引产生死锁的案例里面，有些混淆，锁应该是针对的数据库表的吧，为啥跟索引相关

