sqlserver 中死锁相关问题

死锁定义：

所谓死锁就是两个线程或多个线程在拥有一部分资源的同时还需要拥有其他资源，

但是其他资源被其他线程占有，每个线程为了获得其他线程占有的资源都处于一个相互等待

的状态，这个时候如果没有外界力量破坏这种相互等待的状态或是某个(些)线程自动放弃已经占有的资源，

那么所有的线程都无法完成任务，这个时候系统处于一个僵死状态。这就是所谓的死锁。

sqlserver自身有个锁监视器(Lock monitor),一旦发现死锁，SqlServer会kill掉一个其中一个线程，

使另外一个(些)继续完成任务。该死锁监视器是以一个后台线程的形式存在的，

可以通过系统视图sys.dm_exec_requests查看

select * from sys.dm_exec_requests

where session_id  < 50

但是仅仅依靠SqlServer自身的死锁监视器来解决死锁是不行的，虽然SqlServer暴力解决了死锁的问题

但是这种的暴力解决会影响系统的性能的，对于高并发的性能是不可取的。

接下来来了解下死锁的常见形式，弄清楚产生死锁的原因，然后再通过重写sql或是创建索引或是修改业务逻辑

来解决死锁问题。

创建测试表和数据

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'

死锁的几种表现形式

1.数据行的争用

会话一：

begin tran  

select * from dbo.testklup with(updlock) 

where clskey=1

 

waitfor delay '00:00:10'

update dbo.testklup 

set cont1=200 

where clskey=2

会话二：

begin tran  

select * from dbo.testklup with(updlock) 

where clskey=2

 

waitfor delay '00:00:10'

update dbo.testklup 

set cont1=200 

where clskey=1

先执行会话一，然后执行会话二 ，会出现如下死锁相关信息

产生死锁的原因：

会话一先对clskey=1的数据行添加了一个更新锁,然后它想要对

clskey=2的数据行添加排它锁，但是这个时候clskey=2已被会话二添加了

共享锁，会话二还想对clskey=1 对添加排它锁，但是clskey=1已经被会话一添加了 共享锁.

解决方法：例一种的产生死锁的原因很简单就是没有按照一个固定的顺序访问数据库中的对象，

解决此类死锁问题就需要修改业务逻辑或是sql语句了，按照一个固定的顺序访问数据库中的对象

2.键值的争用。这种争用我理解了很久才弄明白

会话一：

 declare @i int

set @i=100

while 1=1

 begin 

  update testklup  set cont1=@i 

  where clskey=1

  set @i=@i+1

 end

执行计划如下：

会话二：

declare @cont2 char(3000)

while 1=1

begin

    select @cont2=cont2 from testklup  where nlskey=1

end

执行计划如下：

先执行会话一再执行会话二 ，结果如下:

产生死锁的原因：

会话一根据主键clskey=1找到数据行后在数据行添加了一个更新锁(U)，找到数据后，准备修改数据，

更新锁转换为排它锁(X),在更新完表数据后再去更新非聚集索引中的列(cont1)

(create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont1)) ,

所以会话一会对非聚集索引添加排它锁，完成数据的更新。

再看会话二,会话二走非聚集索引获得目标数据行的主键ID，那么会话二就会对非聚集索引添加共享锁，

当会话二获得主键ID后还要通过聚集索引获得列cont2的值，那么回话二就会对主键行添加共享锁

再看下回话一和会话二访问的数据，其实都是同一条数据，

当会话一对非聚集索引添加排它锁，会话二已经对非聚集索引添加了共享锁了，因此会话一等待会话二释放共享锁

当会话二对聚集索引添加共享锁时，会话一已经对聚集索引添加了排它锁，会话二等待会话一释放排它锁

这个时候就死锁了。

解决方案：

方案一：不让会话二访问聚集索引

那么需要修改非聚集create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont2,cont1)

方案二:不让会话一更新非聚集索引

依然需要修改非聚集索引create unique nonclustered index inx_nlcs  on testklup(nlskey)

相关参考链接：

http://www.cnblogs.com/shanksgao/p/3904662.html

转载于:https://blog.51cto.com/dwchaoyue/1569670