解析 SQL Server Deadlocks

最近收到了 Deadlock 的 trace file，如下圖，

如果直接看圖示資訊不夠清楚的話，可以將那個 trc 檔用 Notepad++ 來開，用 xml 方式檢視，就可以看到更多詳細的資料，如下圖，

當然如果這樣資訊又太雜的話，那還有一個方式，就是在那個 Deadlock graph 那列上面按右鍵，選取「擷取事件資料(X)」將它另存成 .xdl 檔，然後用 「SQL Sentry Plan Explorer」 來檢視，如下圖，

案例分享

• Reader-Writer Deadlocks

  當可以看到詳細資料後，就要來開始解析看看是什麼原因造成的，以上面那個例子來說，一個是 update ，另一個是 select ，而 Isolation Level 是 repeatable read 。是典型的 Reader-Writer Deadlocks ，解法如下，

• SELECT 語句不要包在交易之中。
• 檢查 SELECT 語句是否有用到合適的 INDEX (使用 SEEK, 而非 SCAN)，如果已用 SEEK 了，就嘗試下面的解法。
• 使用 row-version based isolation level，例如 READ COMMITTED SNAPSHOT 或是 SNAPSHOT。
• SELECT 語句使用 NOLOCK。

• Writer-Writer Deadlocks

  

  從上圖來看都是針對同一個 Page 想要做 Update 而造成的 Deadlock，另外會有一個比較奇怪的就是「交換事件(exchangeEvent)」，是因為平行處理的原因，可能是沒用到適合的 index，所以才會讓 SQL 使用平行處理。

從上圖可以發現，使用相同的 Store Procedure ，而 Lock 在 Delete ，可以判斷那個 Delete 應該是沒有使用到適合的 index ，所以在 Delete 時，會使用 Scan 的方式去找到要刪除的那一些資料，如下的執行計畫，

可以發現，它是真的是使用 SCAN 的方式。解法如下，

• 建立適合的 index ，例如，

CREATE NONCLUSTERED INDEX NIDX_SRB_QNTSCORE_DATA_CASEID ON dbo.SRB_QNTSCORE_DATA(CASEID);

其他解決方式

除了上述的 Case 的解法外，有其他的解法，

• 更新資料表的順序
  針對 Tables 修改的順序也是要注意，例如 sp1 是更新 tblA 及 tblB ，另一個 sp2 則是先更新 tblB 再更新 tblA ，這樣子的順序交叉，就很容易造成 Deadlock。 所以在 更新的順序上也是要考量到哦!
• Create Indexes with Included Columns
  有一些 Key Lookup deadlock 就是因為在 Update 某 Table 時，除了更新了 Clustered Index 後，還需要再更新 Non Clustered Index，而另一個 Select 除了透過 Non Clustered Index 找到資料後，還需要到 Lookup 到 Clustered Index 取得資料。這樣子就有可能會互相 Lock 到而造成 Deadlock，這種狀況可以加入 Included Columns 來解決。
• MAXDOP
  有些光 SELECT 時，就會發生 Deadlock ，是因為平行處理造成的，解法就是調整 SQL 。如果還會有 Deadlock 的話，就設定 MAXDOP 看看能不能解決。

參考資料

SQL Server Deadlocks by Example