数据库死锁分析和说明:select和update死锁场景举例
背景说明:
表结构和索引情况:
select操作语句和执行计划如下: select [DL_ID] ,[Name],Age from TT_DeadLock where Name ='xxxxxxxx'
update操作语句和执行计划如下::update TT_DeadLock set Name= 'jack1',[LastModifier]='chenzk' ,[LastModifyTime]=GETDATE() where DL_ID='0001FC1D-6DA9-4EDE-91BD-571158477BB3';
验证执行:
背景交代完毕,现在我们分别循环的执行以上两个操作,很快就会发生死锁,如下图:
额外说明:左侧窗口是模拟高频执行update操作,右侧窗口模拟高频执行select操作。其中存储过程定义如下:
ALTER PROC [dbo].[GetAgeByNameProc] @NameStr varchar(100) AS
select [DL_ID] ,[Name],Age from TT_DeadLock where Name =@NameStr
分析原因:
既然确实发生了死锁,那说明存在两个操作互相等待对方持有的锁。
我们先分析Select操作:根据索引和执行计划可以看到,先执行非聚集索引seek,由于select的Age列没有在idx_Name中覆盖,因此要执行key lookup获取Age列。这两步操作隐含了2把锁:idx_Name的S锁和key looup操作中聚集索引的S锁。
然后在看Update操作:由于是update操作因此显然会对主键聚集索引增加X锁,但是这里由于更新的列Name恰好也包含在索引idx_Name中,因此就需要更新idx_Name索引的内容,也就是说也要对idx_Name增加一把X锁。
说清楚了以上两个操作的内部细节,我们将其格式化看看:
Update操作
|
Select操作
|
备注
|
|
第一步
|
更新主键聚集索引--加X锁
|
seek普通索引idx_Name--S锁
|
|
第二步
|
更新普通索引idx_Name--加X锁
|
key lookup操作(主键索引seek)--S锁
|
|
第三步
|
执行结束commit
|
执行结束commit
|
解决办法:
思路就是:打断其中任何一个环节,避免互相等待。
方案一:优化idx_Name索引,实现Age列覆盖
方案二:Update操作和Select操作的执行时增加程序锁,将并发强制改为串行执行。
方案三:Select操作增加with(nolock)【对select数据要求不严格时可用,因为会产生脏数据的问题】