最近遇到了一个看上去很奇怪，分析起来很有意思的死锁问题。这个死锁看上去难以理解。而分析过程中，又使用了很多分析SQL 死锁的典型方法。记录下来整个分析过程还是很有意义的。

问题重现步骤：

经过提炼，问题重现的步骤非常简单，在SQL 2008上可以很容易地重现。

1. 首先，创建一张表格，上面有一个 index，两个non- index。

table tt(id int key,a char(36),b char(36),d (max))

go

index on tt(a)(d)

index on tt(b)(d)

2. 向这个表格里插入10000条记录。

into tt NEWID(),'bbb','ddd'

go 10000

3. 查询某一条记录，找到它的a字段的值。（这里以第十条为例）

* from tt where id = 10

假设我们得到a字段的值是’-EA72-4A40-81DA-’。（这是一个随机值，每次测试会不一样。）

4. 现在按照这个值，对表格进行反复更新。如果两个连接同时运行，死锁就一定会发生。（测试1）

while 1 =1

tt with() set d='cd'

where a='-EA72-4A40-81DA-'

5. 但是如果把两个non- index里的 (d)去掉，然后运行同样的语句循环，死锁就不会发生。（测试2）

drop index on tt

drop index on tt

index on tt(a)(d)

index on tt(b)(d)

6. 或者把d字段的字段类型，从(max)改成(200)，死锁也不会发生。（测试3）

drop index on tt

drop index on tt

alter table tt alter d (200)

index on tt(a)(d)

index on tt(b)(d)

问题分析步骤

为什么两条一模一样的语句，会相互死锁呢？为什么索引上去掉一个选项，或者改掉一个数据类型，就不会死锁了呢？要回答这个问题，还是要先仔细分析一下，死锁是如何发生的。

了解SQL 里死锁发生的直接原因，有两种办法：(1) 收集SQL Trace。(2) 开启1222开关。因为问题可以稳定地在测试环境里重现，我们可以尽可能多地收集信息，把两种方法都用上。

首先我们用下面的脚本打开1222开关。

dbcc (1222, -1)

然后，在运行语句的连接里，运行下面的脚本，了解连接的SPID。后面我们可以用来做SQL Trace的。

@@spid

假设我们得到的结果，一个是54update语句，一个是60。

现在我们开启SQL ，连到SQL 上，新建一个Trace，在选择事件的时候，先勾上”Show all ”、”Show all ”update语句，然后选上Locks下面的这些。

在TSQL下面，选上这些。

点击 ，在SPID上设置只跟踪SPID为54，60，6和20的连接。

然后运行两个连接里的循环，让问题发生。

随后，我们运行指令。可以在SQL 里发现下面的，关于上次死锁的记录。记录比较长，关键的地方我用黄色背景标出。

-list

=

-list

id= =0 =0 =KEY: 8: () =1750 = = =2011-12-01T16:41:39.540 XDES= =X =4 kpid=6380 = spid=60 sbid=0 ecid=0 =0 =2 =2011-12-01T16:41:39.407 =2011-12-01T16:37:13.077 =2011-12-01T16:20:33.170 = SQL - Query =AOBAI =4672 =\ =read (2) = =8 = = =

frame =adhoc line=2 =24 =188 =

8000),@2 (8000)) [tt] WITH() set [d] = @1 WHERE [a]=@2

frame =adhoc line=2 =24 =188 =

tt with() set d='cd'

where a='-EA72-4A40-81DA-

while 1 =1

tt with() set d='cd'

where a='-EA72-4A40-81DA-'

id= =0 =0 =KEY: 8: () =1750 = = =2011-12-01T16:41:39.540 XDES= =U =1 kpid=4200 = spid=54 sbid=0 ecid=0 =0 =2 =2011-12-01T16:41:37.473 =2011-12-01T16:37:19.577 =2011-12-01T16:37:19.577 = SQL - Query =AOBAI =4672 =\ =read (2) = =8 = = =

frame =adhoc line=2 =24 =188 =

8000),@2 (8000)) [tt] WITH() set [d] = @1 WHERE [a]=@2

frame =adhoc line=2 =24 =188 =

tt with() set d='cd'

where a='-EA72-4A40-81DA-

while 1 =1

tt with() set d='cd'

where a='-EA72-4A40-81DA-'

-list

= dbid=8 =.dbo.tt = id= mode=U =

owner-list

owner id= mode=U

-list

id= mode=X =wait

= dbid=8 =.dbo.tt =7F16 id= mode=X =

owner-list

owner id= mode=X

-list

id= mode=U =wait

从里可以看到，死锁的确是发生在两条语句上。连接1（spid=54， id=），另外一个连接2（spid=60， id=）。

分析-list的内容，可以知道死锁发生的直接原因，是连接1在索引上持有一个U-key锁，要申请索引7F16上的U-key锁。而连接2在7F16上有一个X锁，要在索引上申请一个X锁。（SQL Trace里的 Graph事件也能告诉你类似的信息。不过1222的输出可能更全面一些。）

为什么会有这种情况发生呢？1222的输出已经不能告诉我们更多的信息了。我们需要分析SQL Trace里的锁申请和释放记录，来了解当时究竟发生了些什么。

我们先看一次成功地，SQL 是怎么申请和释放锁资源的。Lock:和Lock:这两个event能够告诉我们。不过这两个event的输出比较难读，需要把字段Mode, , 和Type拖到前面来看。

字段里的值，也就是1222输出里的。具体的值和表格、索引名字的对照关系，可以用下面的脚本检查。

o.name ,i.name ,i.type from

sys. i inner join sys. o on i. = o.

inner join sys. p on p. = i. and p. = i.

where p. =

-- 就是其中的一个值。

通过上面这个脚本，我们可以知道出现在SQL Trace里的和实际的索引之间的对应关系：

：7F16

：

:

所以SQL 在Key级别加锁、放锁的顺序是：

索引名

锁类型

申请/释放

阶段

连接1（SPID 54）

连接2（SPID 60）

U

申请

1

J

J

7F16

U

申请

1

L

J

7F16

X

申请

1

J

U

释放

1

J

X

申请

2

L

X

申请

2

X

释放

2

X

释放

2

7F16

X

释放

2

从上图可以看出，语句似乎是分两步执行。第一步SQL先通过索引找到了记录本身（7F16），将其更新。第二步SQL 又更新了两个non- index上的数据，因为这两个索引有了发生修改的d字段。最后，把三个X锁释放掉。前面那个死锁的例子，连接1正运行到第一步，而连接2运行到了第二步。由于连接2在完成第一步以后把上的U锁释放掉了，使得连接1能够开始其第一步的运行，申请到了上的U锁。而连接2马上又要申请上的X锁，两边就相互阻塞住，死锁就这样发生了。

为什么把non- index上的字段去掉，能够解决死锁问题呢（测试2）？我们再用SQL Trace，跟踪一下数据类型修改以后的的执行过程。信息的收集和分析方法跟前面的一样。

索引名

锁类型

申请/释放

U

申请

7F16

U

申请

7F16

X

申请

U

释放

7F16

X

释放

如果把d字段的数据类型从(max)改成(200)会怎么样呢（测试3）？用同样的方法跟踪。

索引名

锁类型

申请/释放

U

申请

7F16

U

申请

7F16

X

申请

U

释放

7F16

X

释放

我们可以看到，不管用那一种方法，都从两步简化成了一步。SQL 只需通过索引找到了记录本身（7F16），将其更新。第二步就不需要做了。这样，死锁就不会发生。

那么为什么会有这种变化呢？要理解SQL 加锁放锁的行为，我们必须分析语句的执行计划。这里，我用在语句前加”set on”的方法，得到一个文字型输出的执行计划。

set on

go

tt with() set d='cd'

where a='-EA72-4A40-81DA-'

测试1：（d (max), 在索引里, 有死锁）

测试2：（d (max), 在索引里没有, 没有死锁）

测试3：（d (200), 在索引里有, 没有死锁）

这三个执行计划很能说明问题。第一个执行计划里，有三个Index 。这也就说明了SQL为什么要申请3个X锁。第二个执行计划里，只有一个Index 。这是因为d字段没有被两个non- index所包含，SQL 只需要更新 index即可。第三个执行计划非常有趣。它只有一次，但是包含了三个。也就是说，SQL一步就把三个index一起更新掉了。所以测试三也是一步做完的，不会有死锁。

分析到这里，问题基本已经比较清楚。我们可以得出以下结论：

1. SQL 的加锁数量、以及顺序，跟语句的执行计划有关系。

2. 表格上的索引越多、数据类型越复杂，执行计划也会越复杂，从而导致遇到阻塞或者死锁的几率增加。

3. 消除死锁，可以先检查语句的执行计划，从调整执行计划入手，引导SQL 申请比较少的锁。

对于这个死锁案例本身，将(max)包含在一个索引里，是不太妥当的。这样会大大增加索引的复杂度，也增加了SQL 的维护成本。解决这个索引的比较好的方法，是将字段d从索引里移除，或者把它改成一个有合适长度限制的类型。