(转)令人无法理解的死锁案例分析
原文地址:http://www.cnblogs.com/nzperfect/archive/2011/09/26/2192006.html
最近遇到一个update时产生死锁的情况,两条一模一样的sql同时执行时,居然会产生Deadlock。
windows 2003 server x64 + sql server 2008 sp2 Enterprise X64
示例(实际环境与该表相似):
当两个进程同时执行下面的语句时,在高并发下会产生死锁:
where a='84B23855-2155-4EE0-911D-38D1265F1655'
示例表结构:
go
create table tt(id int identity primary key,a char(36),b char(36),c varchar(max) ,d char(200))
go
insert into tt select NEWID(),'bbb','ccc','ddd'
go 10000
create index ix_a_bc on tt(a)include(b,c)
使用profiler查看update时,锁请求释放情况:
新开一个会话,在我这里spid=58
where a='84B23855-2155-4EE0-911D-38D1265F1655'
通过profiler观察:
由上图可以看到,update语句首先通过非聚集索引ix_a_bc找到a值为84B23855-2155-4EE0-911D- 38D1265F1655的hash键值是2b02a7cba37c的记录,并加U锁,然后通过该索引上的定位符找到聚集键520072b6acb1,并 加U锁,之后再加X锁,再为2b02a7cba37c加x锁,在这个过程中,为非聚集键及聚集键加了U锁和X锁,其它想update该记录的会话,只能等 待,这样更新完后将释放所有锁。
下面我们为该表再加一索引:
再次执行上面的update语句,通过profiler观察:
通过上图,可以看到在得到聚集键520072b6acb1并为之加U/X锁之后,居然释放掉了非聚集索引键2b02a7cba37c上的U锁,释放之后又再一次的重新获取2b02a7cba37c上的X锁,试想如果在它释放2b02a7cba37cU锁到重新获取x锁这个时间差之间,其它update该行的进程插进来取得2b02a7cba37c上的U锁,将会怎么样?
下面开始测这个情况,在两个会话中执行下面的sql语句,同时更新,在我这里是spid为54 /58 .
由上图可以看到,两个进程同时执行,进程54首先获取到了非聚集键2b02a7cba37c上的U锁,然后得到聚集键520072b6acb1,并 为之加U锁和X锁,接下来进程54释放掉了2b02a7cba37c上的U锁,此时进程58插入进来,获取了该2b02a7cba37c上的U锁,接下来 进程58的动作肯定是要获取聚集键520072b6acb1上的U锁,然而,聚集键520072b6acb1被进程54加了X锁并未释放,所以无法获取, 而进程54接下来又要重新获取2b02a7cba37c上的X锁,而2b02a7cba37c又被进程58加了U锁,互相等待,产生死锁,如下图:
![](https://images.cnblogs.com/cnblogs_com/nzperfect/2011-09-26_162536.png)
在这种情况下,SQL会发现产生Deadlock,自动kill掉一个进程,在本示例中,54进程被kill掉,58进程获取取到聚集键 520072b6acb1上的U锁,然后加X锁,然后释放掉了2b02a7cba37c上的U锁,并再为之加上X锁,再之后释放掉全部锁,进程58完成更 新。如下图:
正常情况下,对同一行执行相同的update时,不会产生死锁,本案例会产生死锁的原因是在获取了非聚集键的U锁,继续要更新非聚集索引时,居然释放了该
键上的U锁,而去重新获取X锁,经过反复测试,发现满足下面的情况时,就会发生这种情况:非聚集索引include列有varchar(max)字段,并
且在update时要更新该字段,而且在这个表里,必须要有两个(多于两个)include该varchar(max)字段的非聚索引时,就会发生。