分析及解決SQLServer死結問題 – ZT

死結，簡而言之，兩個或者多個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，問題就解決了呢？且聽下文分解。

上文中，我們解決了那個情境的死結問題。這次，我們分析一下，為什麼會死結呢？再回顧一下兩個sp的寫法：
   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

   很奇怪吧！p1沒有insert，沒有delete，沒有update，只是一個select，p2才是update。這個和我們前面說過的，trans1裡面updata A，update B；trans2裡面upate B，update A，根本不貼邊啊！
   那麼，什麼導致了死結？

   需要從事件記錄中，看sql的死結資訊：
   Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”): 
   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
   Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”): 
   UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
                
   The SELECT is waiting for a Shared KEY lock on index t1.cidx.  The UPDATE holds a conflicting X lock. 
   The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1.  The SELECT holds a conflicting S lock.

   首先，我們看看p1的執行計畫。怎麼看呢？可以執行set statistics profile on，這句就可以了。下面是p1的執行計畫
   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
        |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
               |--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
                     |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

   我們看到了一個nested loops，第一行，利用索引t1.c2來進行seek，seek出來的那個rowid，在第二行中，用來通過叢集索引來尋找整行的資料。這是什嗎？就是bookmark lookup啊！為什嗎？因為我們需要的c2、c3不能完全的被索引t1.c1帶出來，所以需要書籤尋找。
   好，我們接著看p2的執行計畫。
   UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
         |--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
               |--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
                     |--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...
                           |--Top(ROWCOUNT est 0)
                                 |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

   通過叢集索引的seek找到了一行，然後開始更新。這裡注意的是，update的時候，它會申請一個針對clustered index的X鎖的。

   實際上到這裡，我們就明白了為什麼update會對select產生死結。update的時候，會申請一個針對clustered index的X鎖，這樣就阻塞住了（注意，不是死結！）select裡面最後的那個clustered index seek。死結的另一半在哪裡呢？注意我們的select語句，c2存在於索引idx1中，c1是一個叢集索引cidx。問題就在這裡！我們在p2中更新了c2這個值，所以sqlserver會自動更新包含c2列的非叢集索引：idx1。而idx1在哪裡？就在我們剛才的select語句中。而對這個索引列的更改，意味著索引集合的某個行或者某些行，需要重新排列，而重新排列，需要一個X鎖。
   SO………，問題就這樣被發現了。

   總結一下，就是說，某個query使用非叢集索引來select資料，那麼它會在非叢集索引上持有一個S鎖。當有一些select的列不在該索引上，它需要根據rowid找到對應的叢集索引的那行，然後找到其他資料。而此時，第二個的查詢中，update正在叢集索引上忙乎：定位、加鎖、修改等。但因為正在修改的某個列，是另外一個非叢集索引的某個列，所以此時，它需要同時更改那個非叢集索引的資訊，這就需要在那個非叢集索引上，加第二個X鎖。select開始等待update的X鎖，update開始等待select的S鎖，死結，就這樣發生鳥。

   那麼，為什麼我們增加了一個非叢集索引，死結就消失鳥？我們看一下，按照上文中自動增加的索引之後的執行計畫：
   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
      |--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] >= [@p1] AND [deadlocktest].[dbo].[t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)

   哦，對於clustered index的需求沒有了，因為增加的覆蓋索引已經足夠把所有的資訊都select出來。就這麼簡單。

   實際上，在sqlserver 2005中，如果用profiler來抓eventid：1222，那麼會出現一個死結的圖，很直觀的說。

   下面的方法，有助於將死結減至最少（詳細情況，請看SQLServer線上說明，搜尋：將死結減至最少即可。

• 按同一順序訪問對象。
• 避免事務中的使用者互動。
• 保持事務簡短並處於一個批處理中。
• 使用較低的隔離等級。
• 使用基於資料列版本設定的隔離等級。
  • 將 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON，使得已提交讀事務使用資料列版本設定。
  • 使用快照隔離。
• 使用綁定串連。