mysql复合索引造成的死锁

问题出现

公司的数据库经常出现死锁， 造成服务不稳定，搭载数据库的机器还经常OOM， 到时候只能重启数据库
查询show engine innodb status\G查看死锁信息

t1.png t2.png

可以看到是两条update语句产生了死锁， 其蹊跷之处在于其中第一条只操作了一张表， 另外一条操作了两张表， 乍一看看不出原因， 可以知道的结论是第一条语句锁住了8行， 第二条语句锁住了851243， 但是他们都在等待对方锁住的记录。 查看了一下调用， 其中第二条语句是一个定时任务， 每隔五分钟会执行一次， 第一条语句是随用户点击而执行的， 现在的死锁频率为每天一次， 看上去死锁发生率已经很高了。那我们首先可以来分析一下SQL语句。

SQL语句分析

首先GlobalDiscountCode这张表数据库的隔离级别：REPEATABLE-READ， 索引有

5.png

看一下这条语句的查询结果

QQ截图20170615193050.png

可以看到有13条， 这条UPDATE语句在主键索引上本应该有13条X锁的， 所以可以肯定的是至少有一条主键索引被第二个SQL语句给锁住了

explain一下这条语句看一看

6.png

可以发现它走的索引是userId_code这条复合索引。 那么加锁顺序也是按照这条复合索引来的。所以我们可以得知在userId_code这个索引上加的是gap锁+X锁， 在主键索引上加的是X锁， 通过开始的死锁信息可以看出， 发生死锁的地方是X锁， 所以是在主键索引处。

那我们分析一下第二个SQL语句。 这条语句看起来比较复杂， 会inner join两个表的update

3.png

我们通过改写成select语句来explain一下， 看出对GlobalDiscountCode这张表实际上是走的遍历了全表， 所以理论上来说它要将这张表逐行锁住， 同时也要锁住GlobaOrder这张表中选中的记录， 加锁顺序按照GlobalDiscountCode的主键顺序来的

死锁分析

死锁的成因都是因为加锁顺序的问题， 比较常见的是显示死锁， 也就是两个事物对两把锁上锁顺序不同产生的， 不常见的是两条语句因为采用的索引不同， 导致的加锁顺序不同， 按照何登成的博客上画的可以看出原因

4.png

显然我们这里的死锁成因肯定也是因为用索引导致的加锁顺序不一致， 那么看一看两条语句的加锁顺序吧， 我们参照之前第一条语句的select结果可以知道， 它走的是userId_code这条复合索引， 然后用这条复合索引的时候id是乱序的， 所以对主键索引的加锁也是不按照id顺序来的， 但是第二条语句对主键索引的加锁顺序是按照id顺序的， 所以这两天语句就会产生死锁。

死锁解决

mysql版本为5.5.11时候， 默认会使用复合索引， 而不走单独的索引， 如果是走userId这个索引的话， 对主键索引的加锁就也顺序了， 那么我们需要显示使用userId这个索引就可以解决问题了， 所以更改第一条SQL语句为
update GlobalDiscountCode FORCE INDEX(userId) set readed=1 where userId=? 这样就不会产生死锁了。
找到原因是因为数据库选择了复合索引才导致的死锁， 看来复合索引不能乱加。 会导致加锁顺序乱掉的。

后记

最开始运维和DBA都在纠结于修改第二条SQL语句， 认为那条语句写的不够好， 执行了太长时间， 导致的死锁， 说那条语句里用了效率不高的in， 所以应该把in去掉， 换成六条单独的语句， 但是实际上试了下并没有提高效率， 执行差不多还是一秒钟。 同时第二条SQL语句执行频率很低， 五分钟执行一次， 但是已经造成每天都会有死锁了， 说明死锁出现的几率已经很高了， 即便通过提高效率缩短执行时间， 提高了一两倍也没什么意义。
DBA还想办法缩小锁范围。 但是这个语句实际上是没法拆分的， 如果拆分成两条去写， 也就是先select出来再update， 但是这样就丧失了隔离性， 所以也是不可取的。当然如果按照业务来讲还是有缩小的可能性， 比如说我们可以按照日期排序， 只update最近一周的订单， 这样就会把锁范围缩小很多， 出现死锁的可能性也基本没有了， 因为我们发优惠券的频次一般会大于一周， 这也是解决死锁的一种思路。
考虑到肯定是这两条语句之间的交叉死锁， 而且第二条语句其实相当于表锁， 但是是逐行执行的这一特点（InnoDB不会锁升级）于是我打算暴力一些， 直接把GlobalDiscountCode这个表表锁，写法如下

7.png

这么执行， 效率也是提升了的， 而且理论上也不会产生死锁了， 不过显示锁表担心会出问题， 没敢上线。