MySQL死锁事件排查

#背景
一次购物车线上发版后，观察到线上出现死锁（数据库为MySQL，存储引擎为InnoDB）的错误日志，日志截图如下:

排查

于是根据日志traceId定位是到是某个用户在请求一个购物车的查询接口，且出现死锁的前后用户对购物车只做了这一种查询请求 /api/zcy/trade/middle/carts/orderGroup，100秒内请求12次，3次失败9次成功，失败的3次中2次是因为其他业务接口超时导致，只有1次是数据库表死锁导致，即我们上方看到的数据库死锁错误。

经过确认，此次发版并没有修改该接口，同时通过日志查询到发版前的一周内共计出现过10余次类似错误，因此，该错误与本次版本无关，无需进行回滚处理，继续排查错误。

接口逻辑

/api/zcy/trade/middle/carts/orderGroup接口是对用户在购物车内勾选的商品按照一定的业务逻辑进行分组、计价，最后把用户最终勾选的状态checked 以及分组标识group_mark写入购物车的商品表，（死锁发生在该环节）。

表1 购物车商品表（简版，主要为描述功能）

id	checked	group_mark
1	1	group A
2	1	group A
3	1	group A
4	1	group A
5	0
6	0
7	0
8	1	group B
9	1	group B

借助上表再来讲讲最后这一段写逻辑（事务），我们假定用户小明购物车内有9个商品，这一次小明勾选了1、2、3、4、8、9商品，这时/api/zcy/trade/middle/carts/orderGroup接口的写逻辑是（注意，步骤1-4在一段事务中）

• 步骤1，先查询1-9商品的checked为1的id，如果查询到的数据不为空，则将这些数据的checked置为0，语句为update table_name set checked = 0 where id in (查询到的ids) ；如果查询到的数据为空，也就是用户在执行本次请求之前购物车内未勾选任何商品，无需进行任何操作
• 步骤2，将1、2、3、4、8、9的checked置为1，语句为update table_name set checked = 1 where id in (1,2,3,4,8,9)
• 步骤3，将1-9商品的group_mark全部清空，无论1-9的group_mark是空还是非空，update table_name set group_mark = “” where id in (1,2,3,4,5,6,7,8,9)
• 步骤4，将1、2、3、4、8、9的group_mark分别置为group A 或者group B； 因为步骤3需要对用户的所有商品行1-9按顺序加写锁，因此，无论多少请求并行执行，步骤3都会迫使所有请求串行

猜想

1、所有的写操作都发生在同一张表，且更新操作均是按照主键进行，按照主键进行更新且使用的是InnoDB存储引擎，加的锁为行锁，因此猜测是多次更新时id的顺序不一致导致的死锁，即线程A持有id1的行锁，接下来请求id2的行锁；但线程B先持有id2的行锁，接下来请求id1的行锁
借助线上日志进一步分析，发生死锁的时刻，用户请求了三次接口，用户在操作前，购物车是这个样子的，什么都没有勾选

id	checked	group_mark
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0

用户同时发了三个请求分别为
请求1，勾选的3，
请求2，勾选的2，3
请求3，勾选的1，2，3

请去	步骤1	步骤2	步骤3	步骤4
请求1	购物车勾选记录为空，无处理	设置id为3的字段checked为1，对id为3的数据行进行加行锁，设置checked字段为1	将1-9商品的group_mark全部清空，依次对id为1-9的数据行进行加锁
请求2	购物车勾选记录为空，无处理	设置id为2,3的字段checked为1，依次对id为2，3的数据行进行加行锁（id为3的行锁可能被请求1占用）	将1-9商品的group_mark全部清空，依次对id为1-9的数据行进行加锁
请求3	购物车勾选记录为空，无处理	设置id为1,2,3的字段checked为1，依次对id为1，2，3的数据行进行加行锁（id为2的行锁可能被请求2占用，id为3的行锁可能被请求1或者2占用）	将1-9商品的group_mark全部清空，依次对id为1-9的数据行进行加锁

请求1在步骤2的时候是从id3的开始加行锁，步骤3的时候从id1还是加行锁；加锁顺序为3-1-2-3-4-5-6-7-8-9
请求2在步骤2的时候是从id2的开始加行锁，步骤3的时候从id1还是加行锁；加锁顺序为2-3-1-2-3-4-5-6-7-8-9
请求3在步骤2的时候是从id1的开始加行锁，步骤3的时候从id1还是加行锁；加锁顺序为1-2-3-1-2-3-4-5-6-7-8-9
三个请求均需要对1-9数据加行锁，但是加锁顺序不一致，这就容易造成死锁，例如

• 首先，请求1先对id3进行加锁，然后请求3先对id1进行加锁，均成功；
• 然后，请求1需要id1的行锁，但是id1的行锁被请求3持有；
• 同时，请求3需要id3的行锁，但是id3的行锁被请求1持有；
• 因此死锁。

验证

按照上述分析，在开发环境构造相同场景并用多线程提交3次请求，复现出相同错误

至此，验证猜想

修复方案

方案一

因为步骤3需要对用户的所有商品行1-9按顺序加写锁，而且步骤3必然会执行 ，因此将步骤3调整为最先执行，这样，无论多少并发一起执行，步骤3都会是迫使所有请求串行。经过验证，方案可行

方案二

前端页面适当增加遮罩，避免用户同时发送过多的请求至后端；同时后端加分布式锁，保证写逻辑执行过程串行

总结

1、如何避免死锁
避免死锁的最佳实践通常是确保加锁顺序一致（可参考Alibaba编程规范）；一个事务中对单表操作出现死锁并不常见，当出现多次写操作时需要警惕；MySQL官方死锁案例也提供了一种比较有意思的死锁方式，需要注意
2、MySQL如何解除死锁？
解除死锁需要开启死锁检测，InnoDB会尝试选择相对小的那个事务进行回滚，而判断事务大小的依据是插入、更新、删除的行数

When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

参考文献

https://dev.mysql.com/doc/refman/8.0