sql server中高并发情况下 同时执行select和update语句死锁问题 （二）
来源：程序员人生 发布时间：2015-01-13 09:06:23 阅读次数：5520次
SQL Server死锁使我们常常遇到的问题，数据库操作的死锁是不可避免的，本文其实不打算讨论死锁如何产生，重点在于解决死锁。希望对您学习SQL Server死锁方面能有所帮助。
死锁对DBA或是数据库开发人员而言其实不陌生,它的引发多种多样,1般而言,数据库利用的开发者在设计时都会有1定的考量进而尽可能避免死锁的产生.但有时由于1些特殊利用场景如高频查询,高并发查询下由于数据库设计的潜伏问题,1些不容易捕捉的死锁可能出现从而影响业务.这里为大家介绍由于设计问题引发的键查找死锁及相干的解决办法.
这里我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(固然也能够开启跟踪标记,或利用扩大事件(xevents)等捕捉死锁)
创建测试对象code
<span style="font-size:18px;"><span style="font-size:18px;">create table testklup
( clskey int not null,
nlskey int not null,
cont1 int not null,
cont2 char(3000)
)
create unique clustered index inx_cls on testklup(clskey)
create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1)
insert into testklup select 1,1,100,'aaa'
insert into testklup select 2,2,200,'bbb'
insert into testklup select 3,3,300,'ccc'
</span></span>
开启会话1 摹拟高频update操作
----摹拟高频update操作
<span style="font-size:18px;"><span style="font-size:18px;">declare @i int
set @i=100
while 1=1
begin
update testklup set cont1=@i
where clskey=1
set @i=@i+1
end</span></span>
开启会话2 摹拟高频select操作
----摹拟高频select操作
<span style="font-size:18px;"><span style="font-size:18px;">declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup where nlskey=1
end</span></span>
此时开启会话2履行1小段时间时我们就能够看到类似毛病信息:图1⑴
而在我们开启的跟踪中捕捉到了以下的死锁图.图1⑵
死锁分析:可以看出由于读进程(108)要求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读履行计划图1⑶,写履行计划图1⑷
(由于在默许隔离级别下(读提交)读申请S锁只是瞬间进程,读完立即释放,不会等待事务完成),所以在并发,履行频率不高的情形下不容易出现.但我们摹拟的高频情况使得S锁取得频率非常高,此时就出现了仅仅两个会话,1个读,1个写就造成了死锁现象.
死锁缘由:读操作中的键查找酿成的额外锁(聚集索引)需求
解决方案:在了解了死锁产生的缘由后,解决起来就比较简单了.
我们可以从以下几个方面入手.
a 消除额外的键查找锁需的锁
b 读操作时取消获得锁
a.1我们可以创建覆盖索引使select语句中的查询列包括在指定索引中
<span style="font-size:18px;"><span style="font-size:18px;">CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ( [cont2])</span></span>
a.2 根据查询需求,分步履行,通过聚集索引获得查询列,避免键查找.
<span style="font-size:18px;"><span style="font-size:18px;">declare @cont2 char(3000) declare @clskey intwhile 1=1 begin select @clskey=clskey from testklup where nlskey=1 select @cont2=cont2 from testklup where clskey=@clskey end</span></span>
b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁
<span style="font-size:18px;"><span style="font-size:18px;"> declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup with(nolock) where nlskey=1
end </span></span>
结束语.我们在解决问题时,最好弄清问题的本质缘由,通过问题点寻觅出合适自己的环境的解决方案再实行.
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
