SQL Server中死結介紹

SQL Server中死結介紹

簡介

    什麼是死結？

    我認為，死結是由於兩個對象在擁有一份資源的情況下申請另一份資源，而另一份資源恰好又是這兩對象正持有的，導致兩對象無法完成操作，且所持資源無法釋放。

      什麼又是阻塞？

     阻塞是由於資源不足引起的排隊等待現象。比如同時兩個進程去更新一個表。

    這裡我們可以把阻塞作為死結的必要條件。下面我們先理解一下死結和阻塞再來看一下我最近遇到一個問題以及解決思路。

SQLServer中的死結

     對應到SQL Server中，當在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定資源，此時會造成這些任務永久阻塞，從而出現死結；

   這些資源可能是：單行(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(PAG，8KB)、區結構(EXT，連續的8頁)、堆或B樹(HOBT) 、表(TAB，包括資料和索引)、檔案(File，資料庫檔案)、應用程式專用資源(APP)、中繼資料(METADATA)、配置單位(Allocation_Unit)、整個資料庫(DB)。

    下面我簡單舉一個例子來說明一下死結的原理：

   

 

 ，按步驟執行：

1. begin tran
update test1 set aaa=1

2.

begin tran

update test2 set aaa=1

update test1 set bbb=2

3.再次執行圖1中的Update test2 set bbb=2

執行完成後探索資料並未插入，且一直處於running狀態

 

這個時候我們通過語句查詢死結的進程和語句。得到如下結果：

 

很容易發現發生死結的語句，也可以使用 SQL Server Profiler 分析死結: 將 Deadlock graph 事件類別添加到跟蹤。此事件類別使用死結涉及到的進程和對象的 XML 資料填充跟蹤中的 TextData 資料列。SQL Server 事件探查器 可以將 XML 文檔提取到死結 XML  檔案中，以後可在 SQL Server Management Studio 中查看該檔案。

接下來我們說一下如何處理死結

      1.臨時解決方案，先Kill 掉死結的進程，只是暫時解決這個問題。

      2.SQL Server自動選擇一條SQL作死結犧牲品：當死結發生時，鎖監視器線程執行死結檢查，資料庫引擎 選擇運行復原開銷最小的事務的會話作為死結犧牲品，返回1205 錯誤，復原死結犧牲品的事務並釋放該事務持有的所有鎖，使其他線程的事務可以請求資源並繼續運行。

伺服器: 訊息 1205，層級 13，狀態 50，行 1 事務（進程 ID  xx）與另一個進程已被死結在  lock 資源上，且該事務已被選作死結犧牲品。請重新運行該事務。

    3.使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設定請求逾時。 

    4.在SQLServer 和程式兩個方面都可以做代碼上修正，這裡不在詳細描述，主要是通過發現死結等待一段時間後再次嘗試的方式來解決。

預防和避免死結

    1.盡量減少事務執行的時間。

     2.在合理的範圍內降低隔離等級。

    3.同一個事務內盡量避免出現迴圈對同一個表的處理。

    4.同一個事務內較少使用者互動，即鎖的競爭。

    5.盡量保證邏輯處理的順序比如對錶的處理都按照一個順序進行。

    6.對於需要各種邏輯處理的表，可以通過增加索引的方式來減少鎖的競爭。

    7.盡量減少非叢集索引的include 的列，也能減少外鍵死結的發生。

    8.同一個對象盡量採用select 在update 前來使用。

    9.對於即時性要求不高的可以使用with(nolock)來實現對錶的查詢，但是可能會差生髒讀。

 總結

      本文簡單的介紹了死結的原因，如何解決和預防。當然任何事情都是雙刃劍，還要我們根據實際情況來合理減少死結和阻塞的發生；對於不同隔離界別鎖帶來的問題可以看一下我之前的一篇關於鎖的介紹。希望對死結發生預防和解決有一定的協助。

本文永久更新連結地址：