分析及解决SQLServer死锁问题

  死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：
   trans1                                            trans2
   ------------------------------------------------------------------------
   1.IDBConnection.BeginTransaction   1.IDBConnection.BeginTransaction
   2.update table A                            2.update table B
   3.update table B                            3.update table A
   4.IDBConnection.Commit                4.IDBConnection.Commit

   那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。

   好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。
   -- Batch #1
   CREATE DATABASE deadlocktest
   GO
USE deadlocktest
   SET NOCOUNT ON
   DBCC TRACEON (1222, -1)
   -- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc   
   --traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。
   GO
   IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1
   IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1
   IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2
   GO
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))
   GO
DECLARE @x int
   SET @x = 1
   WHILE (@x <= 1000) BEGIN
            INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)
            SET @x = @x + 1
   END
   GO
CREATE CLUSTERED INDEX cidx ON t1 (c1)
   CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)
   GO
CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
   GO
CREATE PROC p2 @p1 int AS
            UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
            UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
   GO

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

   好，打开一个新的查询窗口，我们开始执行下面的query：
   -- Batch #2
USE deadlocktest
   SET NOCOUNT ON
   WHILE (1=1) EXEC p2 4
   GO

   开始执行后，然后我们打开第三个查询窗口，执行下面的query：
   -- Batch #3
USE deadlocktest
   SET NOCOUNT ON
   CREATE TABLE #t1 (c2 int, c3 int)
   GO
WHILE (1=1) BEGIN
             INSERT INTO #t1 EXEC p1 4
             TRUNCATE TABLE #t1
   END
   GO

   开始执行，哈哈，很快，我们看到了这样的错误信息：
   Msg 1205, Level 13, State 51, Procedure p1, Line 4
   Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

   spid54发现了死锁。
   那么，我们该如何解决它？

   在SqlServer 2005中，我们可以这么做：
1.在trans3的窗口中，选择EXEC p1 4，然后right click，看到了菜单了吗？选择Analyse Query in Database Engine Tuning Advisor。
2.注意右面的窗口中，wordload有三个选择：负载文件、表、查询语句，因为我们选择了查询语句的方式，所以就不需要修改这个radio option了。
3.点左上角的Start Analysis按钮
4.抽根烟，回来后看结果吧！出现了一个分析结果窗口，其中，在Index Recommendations中，我们发现了一条信息：大意是，在表t1上增加一个非聚集索引索引：t2+t1。
5.在当前窗口的上方菜单上，选择Action菜单，选择Apply Recommendations，系统会自动创建这个索引。

   重新运行batch #3，呵呵，死锁没有了。

   这种方式，我们可以解决大部分的Sql Server死锁问题。那么，发生这个死锁的根本原因是什么呢？为什么增加一个non clustered index，问题就解决了呢？且听下文分解。