35讲记⼀次线上SQL死锁事故：如何避免死锁
你好，我是刘超。今天我们来聊聊死锁，开始之前，先分享个⼩故事，相信你可能遇到过，或能从中获得⼀点启发。
之前我参与过⼀个项⽬，在项⽬初期，我们是没有将读写表分离的，⽽是基于⼀个主库完成读写操作。在业务量逐渐增⼤的时候，我们偶尔会收到系统的异常报警信息，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⻓时间等待，你还知道其它⽅法来解决类似问题吗？ 期待在留⾔区看到你的⻅解。也欢迎你点击“请朋友读”，把今天的内容分享给身边的朋友，邀请他⼀起讨论。
精选留⾔ <br />a、<br />1.设置Transaction的超时时间<br />2.设置Transaction的级别为串⾏化级别<br />2019-08-13 01:21<br />我已经设置了昵称<br />⽼师。我们⼀般不会在查询的时候加上for update，我们的组⻓让我们事务中不要放查询语句，只能放插⼊或者更新，就是提前查好，组装好，然后开始执⾏事务。我觉得这其实会出现重复插⼊（并发量⼀⾼就会出现）。请问⽼师事务中真的不能做查询操作吗，还有查询的时候怎么防⽌同时两个事务查不到相对应的数据⽽造成重复插⼊<br />2019-08-13 08:35<br />作者回复<br />for update是⼀种悲观锁实现，我们可以使⽤性能更好的乐观锁来实现，通过版本号来实现数据更新不丢失问题，这种⽅式是最佳选择。
⽽对于插⼊时防重复问题，可以对不允许重复字段设置唯⼀索引，进⾏唯⼀约束，这是⼀种不友好的实现⽅式。
2019-08-13 09:49
Tomcat
⽼师，使⽤ON DUPLICATE KEY UPDATE这个是不是也可以解决这种呢
2019-08-16 10:43
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
请指出问题…
2019-08-16 09:29
奇奇
mysql本身⾃带死锁检测
超时时间在业务上是很难接受的
2019-08-15 13:39
作者回复
是的
2019-08-16 09:30
张学磊
MySQL默认开启了死锁检测机制，当检测到死锁后会选择⼀个最⼩(锁定资源最少得事务)的事务进⾏回滚
2019-08-13 19:16
作者回复
这个回答是我想要的。
Innodb提供了wait-for graph算法来主动进⾏死锁检测，我们可以通过innodb_deadlock_detect = on 打开死锁检测。
2019-08-15 09:50
Fever
兼容矩阵图⾥的Gap锁和横向的Insert Intention不兼容吧。。
2019-08-13 16:04
作者回复
是的，跟竖向是⼀样的
2019-08-13 18:31
陈华应
for update基本不被允许使⽤，除⾮经过review以及测试不会有死锁⻛险
对于锁机制，要很好的了解索引数据结构才能明⽩锁导致的奇怪现象是怎么出现的
2019-08-13 12:38
作者回复
对的
2019-08-15 09:50
若有收获，就点个赞吧
0 人点赞
