解析 SQL Server Deadlocks

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

Deadlock graphDeadlock graph

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

View in Notepad++View in Notepad++

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

saveAs XDL filesaveAs XDL fileView in [SQL Sentry Plan Explorer]View in [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

    Writer-Writer DeadlockWriter-Writer Deadlock

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

View in [SQL Sentry Plan Explorer]View in [SQL Sentry Plan Explorer]

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

Delete的執行計畫Delete的執行計畫

可以發現,它是真的是使用 SCAN 的方式。解法如下,
  • 建立適合的 index ,例如,
    1
    CREATE NONCLUSTERED INDEX NIDX_SRB_QNTSCORE_DATA_CASEID ON dbo.SRB_QNTSCORE_DATA(CASEID);

建立過index的執行計畫建立過index的執行計畫

其他解決方式

除了上述的 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

作者: 亂馬客

亂馬客 @叡揚資訊 rainmaker_ho@gss.com.tw https://rainmakerho.github.io/ https://www.slideshare.net/rainmakerho

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *