致命MySQL死锁：程序员必须警惕！|innodb|key|mysql|死锁|程序员|索引

0 环境

项目初期没做读写分离，基于一个主库完成读写。业务量逐渐增大，偶尔收到系统异常报警信息，DBA 通知我们数据库出现死锁异常。

业务简单，就新增订单、修改订单、查询订单等，为啥会死锁？日志分析，发现是作为幂等性校验的一张表经常出现死锁异常。怀疑是索引导致死锁。后来开发环境模拟相关操作，复现了。

1 复现

创建一张订单记录表，用于校验订单重复创建：

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 数据库的事务提交设置为手动提交，关闭自动提交事务：

mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)

订单做幂等性校验时，先通过订单号检查订单是否存在，如不存在则新增订单记录。

模拟创建产生死锁的运行 SQL 语句。先模拟新建两个订单，并按以下顺序执行幂等性校验 SQL 语句（垂直方向代表执行的时间顺序）：

任务A 任务B BEGIN; BEGIN; SELECT id FROMorder_recordWHEREorder_no= 4 FOR UPDATE;//检查是否存在 order_no等于4的订单

SELECT id FROMorder_recordWHEREorder_no= 5 FOR UPDATE;//检查是否存在 order_no等于5的订单 INSERT INTOorder_record(order_no,status,create_date) VALUES (4,1,'2019-07-13 10:57:03');//如果没有，则插入信息

此时，锁等待中......

INSERT INTOorder_record(order_no,status,create_date) VALUES (5,1,'2019-07-13 10:57:03');//如果没有，则插入信息

此时，锁等待中......

COMMIT;(未完成) COMMIT;(未完成)

此时，发现两个事务已进入死锁状态。可在 information_schema 数据库中查询到具体的死锁情况：

为啥 SELECT 要加 for update 排他锁，而不用共享锁？试想若两个订单号一样的请求同时进来，就有可能出现幻读。即一开始事务 A 中的查询无该订单号，后来事务 B 新增一个该订单号的记录，此时事务 A 再新增一条该订单号记录，就会创建重复的订单记录。面对这种情况，可用锁间隙算法防止幻读。

2 死锁咋产生的？

33讲过并发事务中的锁机制以及行锁的具体实现算法。

行锁的实现算法：

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’);

可通过以下锁的兼容矩阵图，看锁的兼容性：

Gap Insert Intention Record Next-Key Gap 兼容冲突兼容兼容 Insert Intention 冲突兼容兼容冲突 Record 兼容兼容冲突冲突 Next-Key 兼容兼容冲突冲突备注横向是已经持有的锁，纵向是正在请求的锁。

3 避免死锁

知道了死锁问题源自哪儿，就可以找到合适的方法来避免它了。

避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，另一个事务就可以继续执行了。这种方法简单有效，在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的。

另外，我们还可以将 order_no 列设置为唯一索引列。虽然不能防止幻读，但我们可以利用它的唯一性来保证订单记录不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会抛出异常。

我们还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

4 其它常见 SQL 死锁问题

这里再补充一些常见的 SQL 死锁问题，以便你遇到时也能知道其原因，从而顺利解决。

我们知道死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立。所以在一些经常需要使用互斥共用一些资源，且有可能循环等待的业务场景中，要特别注意死锁问题。

接下来，我们再来了解一个出现死锁的场景。

我们讲过，InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果使用辅助索引来更新数据库，就需要使用聚簇索引来更新数据库字段。如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

我们还是以上面的这个订单记录表来重现下聚簇索引和辅助索引更新时，循环等待锁资源导致的死锁问题：

事务A 事务B BEGIN; BEGIN; UPDATEorder_recordSET status = 1 WHEREorder_no= 4 ; UPDATEorder_recordSET status = 1 WHERE id = 4 ;

出现死锁的步骤：

事务A 事务B 首先获取idx_order_status非聚簇索引

获取主键索引的行锁根据非聚族索引获取的主键，获取主键索引的行锁

更新status列时，需要获取idx_order_status非聚簇索引

综上，更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

5 总结

数据库发生死锁的概率并不是很大，一旦遇到了，就一定要彻查具体原因，尽快找出解决方案，老实说，过程不简单。我们只有先对 MySQL 的 InnoDB 存储引擎有足够的了解，才能剖析出造成死锁的具体原因。

例如，以上我例举的两种发生死锁的场景，一个考验的是我们对锁算法的了解，另外一个考验则是我们对聚簇索引和辅助索引的熟悉程度。

解决死锁的最佳方式当然就是预防死锁的发生了，我们平时编程中，可以通过以下一些常规手段来预防死锁的发生：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；