SQL Server死锁问题的解决与分析1
引用：http://database.iyunv.com/art/201006/208602.htm此文章主要向大家讲述的之分析与正确解决SQL Server死锁问题，SQL
Server数据库死锁，通俗的讲就是两个或多个trans，同时请求对方正在请求的某个实际应用对象，而导致双方互相等待。简单的例子如下：
<wbr></wbr>
[*]
trans1<wbr>trans2 <wbr></wbr></wbr>
[*]
IDBConnection.BeginTransaction<wbr>IDBConnection.BeginTransaction
<wbr></wbr></wbr>
[*]
update<wbr>table<wbr>A<wbr>2.update<wbr>table<wbr>B
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
update<wbr>table<wbr>B<wbr>3.update<wbr>table<wbr>A
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
IDBConnection.Commit<wbr>4.IDBConnection.Commit<wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。
<wbr></wbr>
好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。
<wbr></wbr>
<wbr></wbr>
[*]
--<wbr>Batch<wbr>#1
<wbr></wbr></wbr></wbr>
[*]
CREATE<wbr>DATABASE<wbr>deadlocktest
<wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
USE<wbr>deadlocktest <wbr></wbr></wbr>
[*]
SET<wbr>NOCOUNT<wbr>ON
<wbr></wbr></wbr></wbr>
[*]
DBCC<wbr>TRACEON<wbr>(1222,<wbr>-1)<wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc
<wbr></wbr>
traceon(1204,3605,-1)看到所有的SQL Server死锁信息。SqlServer
2005中，对于1204进行了增强，这就是1222。
<wbr></wbr>
<wbr></wbr>
[*]
GO<wbr><wbr></wbr></wbr>
[*]
IF<wbr>OBJECT_ID<wbr>('t1')<wbr>IS<wbr>NOT<wbr>NULL<wbr>DROP<wbr>TABLE<wbr>t1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
IF<wbr>OBJECT_ID<wbr>('p1')<wbr>IS<wbr>NOT<wbr>NULL<wbr>DROP<wbr>PROC<wbr>p1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
IF<wbr>OBJECT_ID<wbr>('p2')<wbr>IS<wbr>NOT<wbr>NULL<wbr>DROP<wbr>PROC<wbr>p2
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
CREATE<wbr>TABLE<wbr>t1<wbr>(c1<wbr>int,<wbr>c2<wbr>int,<wbr>c3<wbr>int,<wbr>c4<wbr>char(5000))
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
DECLARE<wbr>@x<wbr>int
<wbr></wbr></wbr></wbr>
[*]
SET<wbr>@</wbr>x<wbr>=<wbr></wbr></wbr>1<wbr></wbr>
[*]
WHILE<wbr>(@x<wbr></wbr></wbr><</span>=<wbr>1000)<wbr>BEGIN
<wbr></wbr></wbr></wbr>
[*]
INSERT<wbr>INTO<wbr>t1<wbr>VALUES<wbr>(@x*2,<wbr>@x*2,<wbr>@x*2,<wbr>@x*2)
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
SET<wbr>@</wbr>x<wbr>=<wbr>@x<wbr>+<wbr>1
<wbr></wbr></wbr></wbr></wbr></wbr>
[*]
END <wbr></wbr>
[*]
GO <wbr></wbr>
[*]
CREATE<wbr>CLUSTERED<wbr>INDEX<wbr>cidx<wbr>ON<wbr>t1<wbr>(c1)
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
CREATE<wbr>NONCLUSTERED<wbr>INDEX<wbr>idx1<wbr>ON<wbr>t1<wbr>(c2)
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
CREATE<wbr>PROC<wbr>p1<wbr>@p1<wbr>int<wbr>AS<wbr>SELECT<wbr>c2,<wbr>c3<wbr>FROM<wbr>t1<wbr>WHERE<wbr>c2<wbr>BETWEEN<wbr>@p1<wbr>AND<wbr>@p1+1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
CREATE<wbr>PROC<wbr>p2<wbr>@p1<wbr>int<wbr>AS
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2+1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2-1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
GO<wbr></wbr>
<wbr></wbr>
<wbr></wbr>
上述sql创建一个deadlock的示范数据库，插入了1000条数据，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外创建了两个sp，分别是从t1中select数据和update数据。
<wbr></wbr>
好，打开一个新的查询窗口，我们开始执行下面的query：
<wbr></wbr>
<wbr></wbr>
[*]
--<wbr>Batch<wbr>#2
<wbr></wbr></wbr></wbr>
[*]
USE<wbr>deadlocktest <wbr></wbr></wbr>
[*]
SET<wbr>NOCOUNT<wbr>ON
<wbr></wbr></wbr></wbr>
[*]
WHILE<wbr>(</wbr>11=1)<wbr>EXEC<wbr>p2<wbr>4
<wbr></wbr></wbr></wbr></wbr>
[*]
GO<wbr></wbr>
<wbr></wbr>
<wbr></wbr>
开始执行后，然后我们打开第三个查询窗口，执行下面的query：
<wbr></wbr>
<wbr></wbr>
[*]
--<wbr>Batch<wbr>#3
<wbr></wbr></wbr></wbr>
[*]
USE<wbr>deadlocktest <wbr></wbr></wbr>
[*]
SET<wbr>NOCOUNT<wbr>ON
<wbr></wbr></wbr></wbr>
[*]
CREATE<wbr>TABLE<wbr>#t1<wbr>(c2<wbr>int,<wbr>c3<wbr>int)
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
WHILE<wbr>(</wbr>11=1)<wbr>BEGIN
<wbr></wbr></wbr>
[*]
INSERT<wbr>INTO<wbr>#t1<wbr>EXEC<wbr>p1<wbr>4
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
TRUNCATE<wbr>TABLE<wbr>#t1
<wbr></wbr></wbr></wbr>
[*]
END <wbr></wbr>
[*]
GO<wbr></wbr>
<wbr></wbr>
<wbr></wbr>
开始执行，哈哈，很快，我们看到了这样的错误信息：
<wbr></wbr>
<wbr></wbr>
[*]
Msg<wbr>1205,<wbr>Level<wbr>13,<wbr>State<wbr>51,<wbr>Procedure<wbr>p1,<wbr>Line<wbr>4
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
Transaction<wbr>(Process<wbr>ID<wbr>54)<wbr>was<wbr>deadlocked<wbr>on<wbr>lock<wbr>resources<wbr>with<wbr>another
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
<wbr>process<wbr>and<wbr>has<wbr>been<wbr>chosen<wbr>as<wbr>the<wbr>deadlock<wbr>victim.<wbr>Rerun<wbr>the<wbr>transaction.<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
spid54发现了SQL Server死锁。
<wbr></wbr>
那么，我们该如何解决它？
<wbr></wbr>
在SqlServer 2005中，我们可以这么做：
<wbr></wbr>
1.在trans3的窗口中，选择EXEC p1 4，然后right click，看到了菜单了吗？选择Analyse Query in
Database Engine Tuning Advisor。
<wbr></wbr>
2.注意右面的窗口中，wordload有三个选择：负载文件、表、查询语句，因为我们选择了查询语句的方式，所以就不需要修改这个radio
option了。
<wbr></wbr>
3.点左上角的Start Analysis按钮
<wbr></wbr>
4.抽根烟，回来后看结果吧！出现了一个分析结果窗口，其中，在Index
Recommendations中，我们发现了一条信息：大意是，在表t1上增加一个非聚集索引索引：t2+t1。
<wbr></wbr>
5.在当前窗口的上方菜单上，选择Action菜单，选择Apply Recommendations，系统会自动创建这个索引。
<wbr></wbr>
重新运行batch #3，呵呵，死锁没有了。
<wbr></wbr>
这种方式，我们可以解决大部分的Sql Server死锁问题。那么，发生这个死锁的根本原因是什么呢？为什么增加一个non
clustered index，问题就解决了呢？
<wbr></wbr>
这次，我们分析一下，为什么会SQL Server死锁呢？再回顾一下两个sp的写法：
<wbr></wbr>
<wbr></wbr>
[*]
CREATE<wbr>PROC<wbr>p1<wbr>@p1<wbr>int<wbr>AS<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
SELECT<wbr>c2,<wbr>c3<wbr>FROM<wbr>t1<wbr>WHERE<wbr>c2<wbr>BETWEEN<wbr>@p1<wbr>AND<wbr>@p1+1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
GO <wbr></wbr>
[*]
CREATE<wbr>PROC<wbr>p2<wbr>@p1<wbr>int<wbr>AS
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2+1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2-1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
GO<wbr></wbr>
<wbr></wbr>
<wbr></wbr>
很奇怪吧！p1没有insert，没有delete，没有update，只是一个select，p2才是update。这个和我们前面说过的，trans1里面updata
A，update B；trans2里面upate B，update A，根本不贴边啊！
<wbr></wbr>
那么，什么导致了死锁？
<wbr></wbr>
需要从事件日志中，看sql的死锁信息：
<wbr></wbr>
<wbr></wbr>
[*]
Spid<wbr>X<wbr>is<wbr>running<wbr>this<wbr>query<wbr>(line<wbr>2<wbr>of<wbr>proc<wbr>,<wbr>inputbuffer<wbr>“…<wbr>EXEC<wbr>p1<wbr>4<wbr>…”):<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
SELECT<wbr>c2,<wbr>c3<wbr>FROM<wbr>t1<wbr>WHERE<wbr>c2<wbr>BETWEEN<wbr>@p1<wbr>AND<wbr>@p1+1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
Spid<wbr>Y<wbr>is<wbr>running<wbr>this<wbr>query<wbr>(line<wbr>2<wbr>of<wbr>proc<wbr>,<wbr>inputbuffer<wbr>“EXEC<wbr>p2<wbr>4”):<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2+1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
The<wbr>SELECT<wbr>is<wbr>waiting<wbr>for<wbr>a<wbr>Shared<wbr>KEY<wbr>lock<wbr>on<wbr>index<wbr>t1.cidx.<wbr>The<wbr>UPDATE<wbr>holds<wbr>a<wbr>conflicting<wbr>X<wbr>lock.<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
The<wbr>UPDATE<wbr>is<wbr>waiting<wbr>for<wbr>an<wbr>eXclusive<wbr>KEY<wbr>lock<wbr>on<wbr>index<wbr>t1.idx1.<wbr>The<wbr>SELECT<wbr>holds<wbr>a<wbr>conflicting<wbr>S<wbr>lock.<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
首先，我们看看p1的执行计划。怎么看呢？可以执行set statistics profile
on，这句就可以了。下面是p1的执行计划
<wbr></wbr>
<wbr></wbr>
[*]
SELECT<wbr>c2,<wbr>c3<wbr>FROM<wbr>t1<wbr>WHERE<wbr>c2<wbr>BETWEEN<wbr>@p1<wbr>AND<wbr>@p1+1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
|--Nested<wbr>Loops(Inner<wbr>Join,<wbr>OUTER<wbr>REFERENCES:(,<wbr>.))
<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
|--Index<wbr>Seek(OBJECT:(.),<wbr>SEEK:(.<wbr></wbr></wbr></wbr>>=<wbr>[@p1]<wbr>AND<wbr>.<wbr></wbr></wbr></wbr></wbr><</span>=<wbr>[@p1]+(1))<wbr>ORDERED<wbr>FORWARD)
<wbr></wbr></wbr></wbr></wbr>
[*]
|--Clustered<wbr>Index<wbr>Seek(OBJECT:(.),<wbr>SEEK:(.=.<wbr>AND<wbr>=)<wbr>LOOKUP<wbr>ORDERED<wbr>FORWARD)<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
我们看到了一个nested
loops，第一行，利用索引t1.c2来进行seek，seek出来的那个rowid，在第二行中，用来通过聚集索引来查找整行的数据。这是什么？就是bookmark
lookup啊！为什么？因为我们需要的c2、c3不能完全的被索引t1.c1带出来，所以需要书签查找。
<wbr></wbr>
好，我们接着看p2的执行计划。
<wbr></wbr>
<wbr></wbr>
[*]
UPDATE<wbr>t1<wbr>SET<wbr></wbr></wbr></wbr>c2c2<wbr>=<wbr>c2+1<wbr>WHERE<wbr></wbr></wbr></wbr></wbr>c1<wbr>=<wbr>@p1
<wbr></wbr></wbr></wbr>
[*]
|--Clustered<wbr>Index<wbr>Update(OBJECT:(.),<wbr>OBJECT:(.),<wbr>SET:(.<wbr>=<wbr>))
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
|--Compute<wbr>Scalar(DEFINE:(=))
<wbr></wbr></wbr>
[*]
|--Compute<wbr>Scalar(DEFINE:(=.+(1),<wbr>=CASE<wbr>WHEN<wbr>CASE<wbr>WHEN<wbr>...
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
|--Top(ROWCOUNT<wbr>est<wbr>0)
<wbr></wbr></wbr></wbr>
[*]
|--Clustered<wbr>Index<wbr>Seek(OBJECT:(.),<wbr>SEEK:(.=[@p1])<wbr>ORDERED<wbr>FORWARD)<wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
通过聚集索引的seek找到了一行，然后开始更新。这里注意的是，update的时候，它会申请一个针对clustered
index的X锁的。
<wbr></wbr>
实际上到这里，我们就明白了为什么update会对select产生SQL
Server死锁。update的时候，会申请一个针对clustered
index的X锁，这样就阻塞住了（注意，不是死锁！）select里面最后的那个clustered index seek。
死锁的另一半在哪里呢？注意我们的select语句，c2存在于索引idx1中，c1是一个聚集索引cidx。问题就在这里！我们在p2中更新了c2这个值，所以sqlserver会自动更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我们刚才的select语句中。而对这个索引列的更改，意味着索引集合的某个行或者某些行，需要重新排列，而重新排列，需要一个X锁。
<wbr></wbr>
SO………，问题就这样被发现了。
<wbr></wbr>
总结一下，就是说，某个query使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。
而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁，就这样发生鸟。
<wbr></wbr>
那么，为什么我们增加了一个非聚集索引，死锁就消失鸟？我们看一下，按照上文中自动增加的索引之后的执行计划：
<wbr></wbr>
<wbr></wbr>
[*]
SELECT<wbr>c2,<wbr>c3<wbr>FROM<wbr>t1<wbr>WHERE<wbr>c2<wbr>BETWEEN<wbr>@p1<wbr>AND<wbr>@p1+1
<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
[*]
|--Index<wbr>Seek(OBJECT:(...<br style="clear: both; width: 0px; height: 0px;">),<wbr>SEEK:(...<br style="clear: both; width: 0px; height: 0px;"><wbr></wbr></wbr></wbr>>=<wbr>[@p1]<wbr>AND<wbr>...<wbr></wbr></wbr></wbr></wbr><</span>=<wbr>[@p1]+(1))<wbr>ORDERED<wbr>FORWARD)<wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr></wbr>
哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。
<wbr></wbr>
实际上，在sqlserver
2005中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。
<wbr></wbr>
下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将SQL Server死锁减至最少即可。
<wbr></wbr>
按同一顺序访问对象。
<wbr></wbr>
避免事务中的用户交互。
<wbr></wbr>
保持事务简短并处于一个批处理中。
<wbr></wbr>
使用较低的隔离级别。
<wbr></wbr>
使用基于行版本控制的隔离级别。
<wbr></wbr>
将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
<wbr></wbr>
使用快照隔离。
<wbr></wbr>
使用绑定连接。
页:
[1]