死锁场景：双Update操作的情况

遇到一个比较有代表性的死锁问题，在这里记录并分享出来，希望能给大家起到参考作用。

问题的表现就是程序中以较低的频率出现死锁异常，下面重点说一下排查分析过程和死锁原因：

分析排查过程：

首先，梳理搜索整个项目，共发现以下两处存在并发的update操作：
1、加积分的操作：

UPDATE AMS_UserLevelInfo SET ULI_Score=#UserScore#,LastModifier=#LastModifier#,LastModifyTime=#LastModifyTime# WHERE ULI_ID=#ULIID#

2、加电量的操作：

UPDATE AMS_UserLevelInfo SET LastModifier='chenzk',LastModifyTime=getdate(), ULI_ALLChargeSum= ULI_ALLChargeSum+1, ULI_LVProtecting= 'xxxxx' WHERE ULI_UserID=#UserId# AND DelFlag=0

然后通过运行日志分析确认发现：

• 2个操作发生在同一秒：2020-08-26 17:41:19.487
• 2条链路的耗时都在4.7s左右，耗时长度相似，侧面印证了死锁等待的时间。

项目背景说明：

表的索引列和include列的情况：

加积分的update操作执行计划：

留意看：由于索引idx_AMS_UserLevelInfo_ULI_UserID包含了ULI_Score列，因此此update操作会更新索引idx_AMS_UserLevelInfo_ULI_UserID

加电量的update操作执行计划：

包含的key lookup操作的详情：

索引更新的详情：

根据以上执行计划，我们梳理成为表格后看看：

	加积分操作	加电量操作
step1	seek主键索引PK_AMS_USERLEVELINFO--S锁	seek索引idx_AMS_UserLevelInfo_ULI_UserID--S锁、 seek主键索引PK_AMS_USERLEVELINFO--S锁	S锁，都可执行。
step2	更新主键索引PK_AMS_USERLEVELINFO--X锁	更新主键索引PK_AMS_USERLEVELINFO--X锁	X锁
step3	更新其它索引（idx_AMS_UserLevelInfo_ULI_UserID、idx_ams_userlevelinfo_LastModifyTime）--X锁	更新其它索引（idx_AMS_UserLevelInfo_LV2Name、idx_ams_userlevelinfo_LastModifyTime）--X锁
step4	结束commit	结束commit

进一步的：由于idx_ams_userlevelinfo_LastModifyTime、idx_AMS_UserLevelInfo_LV2Name俩索引更新没有交集，将表格简化后：

	加积分操作	加电量操作
step1	seek主键索引PK_AMS_USERLEVELINFO--S锁	seek索引idx_AMS_UserLevelInfo_ULI_UserID--S锁、 seek主键索引PK_AMS_USERLEVELINFO--S锁
step2	更新主键索引PK_AMS_USERLEVELINFO--X锁	更新主键索引PK_AMS_USERLEVELINFO--X锁
step3	更新其它索引（idx_AMS_UserLevelInfo_ULI_UserID）--X锁
step4	结束commit	结束commit

以上表格在并发时就构成了死锁的条件：

• 加积分操作的step3由于要加X锁，因此在等待加电量操作的Step1中idx_AMS_UserLevelInfo_ULI_UserID的S锁释放。
• 加电量操作的Step2由于要加X锁，因此在等待加积分操作的Step2中PK_AMS_USERLEVELINFO的X锁的释放。

死锁复现环节：

分别在两个窗口中模拟高频运行操作，很快就能出现死锁异常：

解决方案

根本思路就是：打断互相等待的步骤，使其无法满足死锁的条件。
方案一：idx_AMS_UserLevelInfo_ULI_UserID索引排除掉列ULI_Score，这样加积分操作就避免了更新索引idx_AMS_UserLevelInfo_ULI_UserID。
调整索引后的执行计划如下图：

可以看到持续执行，并没有死锁发生：

方案二：【X不可行的方案】调整索引idx_AMS_UserLevelInfo_ULI_UserID，使其include更多列，避免回表key lookup。【虽然避免了key lookup，但是由于自身是update语句因此后面还有更新PK_AMS_USERLEVELINFO的X锁】
方案三：如果无法打断互相等待的步骤，那么就只能在程序执行的入口处通过程序锁来避免并发执行，强制改为串行操作。

结果验证：

采用方案1优化并发布后，过了几天观察异常日志，可以确认死锁问题已修复：

总结提炼：

• 索引中include太多列也不好，因为这些列的值一旦更新就需要同时去更新维护对应的索引，就构成了死锁的必要条件。【PS：MySQL就没有include的特性。】
• 双Update操作死锁场景一句话说明原因：update的列恰好会造成更新对方update使用的索引。【这也印证了：更新频繁的列不适合放到索引中！！】