如何深入解析mssql在高频高并发访问时键查找死锁问题

如何深入解析mssql在高频高并发访问时键查找死锁问题，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

死锁对于DBA或是数据库开发人员而言并不陌生,它的引发多种多样,一般而言,数据库应用的开发者在设计时都会有一定的考量进而尽量避免死锁的产生.但有时因为一些特殊应用场景如高频查询,高并发查询下由于数据库设计的潜在问题,一些不易捕捉的死锁可能出现从而影响业务.这里为大家介绍由于设计问题引起的键查找死锁及相关的解决办法.

这里我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(当然也可以开启跟踪标记,或者应用扩展事件(xevents)等捕捉死锁)

创建测试对象code

create table testklup(clskey int not null,nlskey int not null,cont1 int not null,cont2 char(3000))create unique clustered index inx_cls ontestklup(clskey)create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1)insert into testklup select 1,1,100,aaainsert into testklup select 2,2,200,bbbinsert into testklup select 3,3,300,ccc

开启会话1 模拟高频update操作

----模拟高频update操作

 declare @i intset @i=100while 1=1 begin  update testklup set cont1=@i  where clskey=1 set @i=@i+1end

开启会话2 模拟高频select操作

----模拟高频select操作

declare @cont2 char(3000)while 1=1begin  select @cont2=cont2 fromtestklupwhere nlskey=1end

此时开启会话2执行一小段时间时我们就可以看到类似错误信息:图1-1

图1-1

而在我们开启的跟踪中捕捉到了如下的死锁图.图1-2

图1-2

死锁分析:可以看出由于读进程(108)请求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读执行计划图1-3,写执行计划图1-4

(由于在默认隔离级别下(读提交)读申请S锁只是瞬间过程,读完立即释放,不会等待事务完成),所以在并发,执行频率不高的情形下不易出现.但我们模拟的高频情况使得S锁获得频率非常高,此时就出现了仅仅两个会话,一个读,一个写就造成了死锁现象.

图1-3

图1-4死锁原因:读操作中的键查找造成的额外锁(聚集索引)需求

解决方案:在了解了死锁产生的原因后,解决起来就比较简单了.

我们可以从以下几个方面入手.

a 消除额外的键查找锁需的锁

b 读操作时取消获取锁

a.1我们可以创建覆盖索引使select语句中的查询列包含在指定索引中

CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]([nlskey] ASC) INCLUDE ( [cont2])

a.2 根据查询需求,分步执行,通过聚集索引获取查询列,避免键查找.

declare @cont2 char(3000)declare@clskey intwhile1=1begin  select @clskey=clskey from testklup where nlskey=1  select @cont2=cont2 from testklup whereclskey=@clskeyend

b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁

declare @cont2 char(3000)while 1=1begin  select @cont2=cont2 fromtestklupwith(nolock) where nlskey=1end

看完上述内容，你们掌握如何深入解析mssql在高频高并发访问时键查找死锁问题的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！