令人无法理解的死锁案例分析

最近遇到一个update时产生死锁的情况，两条一模一样的sql同时执行时，居然会产生Deadlock。

 

windows 2003 server x64 + sql server 2008 sp2 Enterprise X64

 

示例(实际环境与该表相似)：

当两个进程同时执行下面的语句时，在高并发下会产生死锁：

update tt with(rowlock) set c='eb',d='cc' 
where a='84B23855-2155-4EE0-911D-38D1265F1655'

 

示例表结构：

use tempdb
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

update tt with(rowlock) set c='eb',d='cc' 
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该记录的会话，只能等待，这样更新完后将释放所有锁。

 

下面我们为该表再加一索引：

create index ix_b_cd on tt(b)include(c,d)

再次执行上面的update语句，通过profiler观察：

 

通过上图，可以看到在得到聚集键520072b6acb1并为之加U/X锁之后，居然释放掉了非聚集索引键2b02a7cba37c上的U锁，释放之后又再一次的重新获取2b02a7cba37c上的X锁，试想如果在它释放2b02a7cba37cU锁到重新获取x锁这个时间差之间，其它update该行的进程插进来取得2b02a7cba37c上的U锁，将会怎么样？

 

下面开始测这个情况，在两个会话中执行下面的sql语句，同时更新，在我这里是spid为54 /58 .

 

通过上图，我们发现，产生了死锁，产生死锁后，进程54成为牺牲品，下面看用sql profiler抓到的情况：

 

 

由上图可以看到，两个进程同时执行，进程54首先获取到了非聚集键2b02a7cba37c上的U锁，然后得到聚集键520072b6acb1，并为之加U锁和X锁，接下来进程54释放掉了2b02a7cba37c上的U锁，此时进程58插入进来，获取了该2b02a7cba37c上的U锁，接下来进程58的动作肯定是要获取聚集键520072b6acb1上的U锁，然而，聚集键520072b6acb1被进程54加了X锁并未释放，所以无法获取，而进程54接下来又要重新获取2b02a7cba37c上的X锁，而2b02a7cba37c又被进程58加了U锁，互相等待，产生死锁，如下图：

在这种情况下，SQL会发现产生Deadlock,自动kill掉一个进程，在本示例中，54进程被kill掉，58进程获取取到聚集键520072b6acb1上的U锁，然后加X锁，然后释放掉了2b02a7cba37c上的U锁，并再为之加上X锁，再之后释放掉全部锁，进程58完成更新。如下图：

正常情况下，对同一行执行相同的update时，不会产生死锁，本案例会产生死锁的原因是在获取了非聚集键的U锁，继续要更新非聚集索引时，居然释放了该键上的U锁，而去重新获取X锁，经过反复测试，发现满足下面的情况时，就会发生这种情况：非聚集索引include列有varchar(max)字段，并且在update时要更新该字段，而且在这个表里，必须要有两个(多于两个)include该varchar(max)字段的非聚索引时，就会发生。

 

这是bug，还是故意为之？为何要针对varchar(max)会有此不同？