死锁场景:双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使用的索引。【这也印证了:更新频繁的列不适合放到索引中!!】