`
az7772010
  • 浏览: 201909 次
  • 性别: Icon_minigender_1
  • 来自: 天津
社区版块
存档分类
最新评论

SQL Server死锁问题的解决与分析1

 
阅读更多
引用:http://database.51cto.com/art/201006/208602.htm

此文章主要向大家讲述的之分析与正确解决SQL Server死锁问题,SQL Server数据库死锁,通俗的讲就是两个或多个trans,同时请求对方正在请求的某个实际应用对象,而导致双方互相等待。简单的例子如下:

<wbr></wbr>


  1. trans1<wbr>trans2 <wbr></wbr></wbr>
  2. IDBConnection.BeginTransaction<wbr>IDBConnection.BeginTransaction <wbr></wbr></wbr>
  3. update<wbr>table<wbr>A<wbr>2.update<wbr>table<wbr>B <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  4. update<wbr>table<wbr>B<wbr>3.update<wbr>table<wbr>A <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  5. 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>


  1. --<wbr>Batch<wbr>#1 <wbr></wbr></wbr></wbr>
  2. CREATE<wbr>DATABASE<wbr>deadlocktest <wbr></wbr></wbr></wbr>
  3. GO <wbr></wbr>
  4. USE<wbr>deadlocktest <wbr></wbr></wbr>
  5. SET<wbr>NOCOUNT<wbr>ON <wbr></wbr></wbr></wbr>
  6. 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>


  1. GO<wbr><wbr></wbr></wbr>
  2. 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>
  3. 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>
  4. 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>
  5. GO <wbr></wbr>
  6. 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>
  7. GO <wbr></wbr>
  8. DECLARE<wbr>@x<wbr>int <wbr></wbr></wbr></wbr>
  9. SET<wbr>@</wbr>x<wbr>=<wbr></wbr></wbr>1<wbr></wbr>
  10. WHILE<wbr>(@x<wbr></wbr></wbr><</span>=<wbr>1000)<wbr>BEGIN <wbr></wbr></wbr></wbr>
  11. 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>
  12. SET<wbr>@</wbr>x<wbr>=<wbr>@x<wbr>+<wbr>1 <wbr></wbr></wbr></wbr></wbr></wbr>
  13. END <wbr></wbr>
  14. GO <wbr></wbr>
  15. CREATE<wbr>CLUSTERED<wbr>INDEX<wbr>cidx<wbr>ON<wbr>t1<wbr>(c1) <wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  16. CREATE<wbr>NONCLUSTERED<wbr>INDEX<wbr>idx1<wbr>ON<wbr>t1<wbr>(c2) <wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  17. GO <wbr></wbr>
  18. 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>
  19. GO <wbr></wbr>
  20. CREATE<wbr>PROC<wbr>p2<wbr>@p1<wbr>int<wbr>AS <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  21. 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>
  22. 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>
  23. GO<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

上述sql创建一个deadlock的示范数据库,插入了1000条数据,并在表t1上建立了c1列的聚集索引,和c2列的非聚集索引。另外创建了两个sp,分别是从t1中select数据和update数据。

<wbr></wbr>

好,打开一个新的查询窗口,我们开始执行下面的query:

<wbr></wbr>

<wbr></wbr>


  1. --<wbr>Batch<wbr>#2 <wbr></wbr></wbr></wbr>
  2. USE<wbr>deadlocktest <wbr></wbr></wbr>
  3. SET<wbr>NOCOUNT<wbr>ON <wbr></wbr></wbr></wbr>
  4. WHILE<wbr>(</wbr>11=1)<wbr>EXEC<wbr>p2<wbr>4 <wbr></wbr></wbr></wbr></wbr>
  5. GO<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

开始执行后,然后我们打开第三个查询窗口,执行下面的query:

<wbr></wbr>

<wbr></wbr>


  1. --<wbr>Batch<wbr>#3 <wbr></wbr></wbr></wbr>
  2. USE<wbr>deadlocktest <wbr></wbr></wbr>
  3. SET<wbr>NOCOUNT<wbr>ON <wbr></wbr></wbr></wbr>
  4. CREATE<wbr>TABLE<wbr>#t1<wbr>(c2<wbr>int,<wbr>c3<wbr>int) <wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  5. GO <wbr></wbr>
  6. WHILE<wbr>(</wbr>11=1)<wbr>BEGIN <wbr></wbr></wbr>
  7. INSERT<wbr>INTO<wbr>#t1<wbr>EXEC<wbr>p1<wbr>4 <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  8. TRUNCATE<wbr>TABLE<wbr>#t1 <wbr></wbr></wbr></wbr>
  9. END <wbr></wbr>
  10. GO<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

开始执行,哈哈,很快,我们看到了这样的错误信息:

<wbr></wbr>

<wbr></wbr>


  1. 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>
  2. 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>
  3. <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>


  1. CREATE<wbr>PROC<wbr>p1<wbr>@p1<wbr>int<wbr>AS<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  2. 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>
  3. GO <wbr></wbr>
  4. CREATE<wbr>PROC<wbr>p2<wbr>@p1<wbr>int<wbr>AS <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  5. 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>
  6. 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>
  7. 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>


  1. Spid<wbr>X<wbr>is<wbr>running<wbr>this<wbr>query<wbr>(line<wbr>2<wbr>of<wbr>proc<wbr>[p1],<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>
  2. 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>
  3. Spid<wbr>Y<wbr>is<wbr>running<wbr>this<wbr>query<wbr>(line<wbr>2<wbr>of<wbr>proc<wbr>[p2],<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>
  4. 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>
  5. 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>
  6. 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>


  1. 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>
  2. |--Nested<wbr>Loops(Inner<wbr>Join,<wbr>OUTER<wbr>REFERENCES:([Uniq1002],<wbr>[t1].[c1])) <wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  3. |--Index<wbr>Seek(OBJECT:([t1].[idx1]),<wbr>SEEK:([t1].[c2]<wbr></wbr></wbr></wbr>>=<wbr>[@p1]<wbr>AND<wbr>[t1].[c2]<wbr></wbr></wbr></wbr></wbr><</span>=<wbr>[@p1]+(1))<wbr>ORDERED<wbr>FORWARD) <wbr></wbr></wbr></wbr></wbr>
  4. |--Clustered<wbr>Index<wbr>Seek(OBJECT:([t1].[cidx]),<wbr>SEEK:([t1].[c1]=[t1].[c1]<wbr>AND<wbr>[Uniq1002]=[Uniq1002])<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>


  1. 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>
  2. |--Clustered<wbr>Index<wbr>Update(OBJECT:([t1].[cidx]),<wbr>OBJECT:([t1].[idx1]),<wbr>SET:([t1].[c2]<wbr>=<wbr>[Expr1004])) <wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  3. |--Compute<wbr>Scalar(DEFINE:([Expr1013]=[Expr1013])) <wbr></wbr></wbr>
  4. |--Compute<wbr>Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1),<wbr>[Expr1013]=CASE<wbr>WHEN<wbr>CASE<wbr>WHEN<wbr>... <wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  5. |--Top(ROWCOUNT<wbr>est<wbr>0) <wbr></wbr></wbr></wbr>
  6. |--Clustered<wbr>Index<wbr>Seek(OBJECT:([t1].[cidx]),<wbr>SEEK:([t1].[c1]=[@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>


  1. 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>
  2. |--Index<wbr>Seek(OBJECT:([deadlocktest].[dbo].[t1].<br style="clear: both; width: 0px; height: 0px;"> [_dta_index_t1_7_2073058421__K2_K1_3]),<wbr>SEEK:([deadlocktest].[dbo].[t1].[c2]<br style="clear: both; width: 0px; height: 0px;"><wbr></wbr></wbr></wbr>>=<wbr>[@p1]<wbr>AND<wbr>[deadlocktest].[dbo].[t1].[c2]<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>

使用绑定连接。

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics