SQL Server死結的分析、處理與預防

標籤：sql server   資料庫伺服器   效能   database   最佳化   

1、 基本原理 

所謂“死結”，在作業系統的定義是：在一組進程中的各個進程均佔有不會釋放的資源，但因互相申請被其他進程所站用不會釋放的資源而處於的一種永久等待狀態。 

定義比較抽象，可以協助你比較直觀的理解死結：

出現死結需要滿足幾個必要條件： a）互斥：進程獨佔資源，資源不共用； 

b）請求與保持：已經得到資源的進程可以再次申請新資源； c）不剝奪：已指派的資源不能被其它進程強制剝奪； 

d）環路等待：幾個進程組成環路，都在相互等待正被佔用的資源； 

對應到SQL Server中，在2個或多個任務中（insert、update、delete、select、alter table或Tran事務等等），如果每個任務鎖定了其它任務想要鎖定資源，會造成這些任務永久阻塞，從而出現死結。這些資源可能是：單行資料（RID、HEAP堆中的行）、索引中的鍵（KEY，行鎖）、頁（Page，8KB）、區（Extent，8個連續頁）、堆或B樹、表（Table，資料和索引）、檔案（File，資料庫檔案）、整個資料庫（DataBase）。 

如果系統中的資源不足或者資源分派策略不當，會導致因進程間的資源爭用產生死結現象。但更多的可能是程式員的程式有問題。“鎖”有多種方式，如意圖鎖定、共用鎖定、獨佔鎖定等等。鎖還有多種粒度，如行鎖、表鎖。 

瞭解了死結產生的原因，就可以最大可能的避免與預防死結。只要上述4個必要條件中有1個不滿足，就不會發生死結。所以，在系統設計、實現階段就可以在資源分派與佔用、資源訪問順序等方面採取必要措施。 

2、 一個例子 

直面死結，來看一個例子：1所示，建立一個查詢時段，並利用事務的原子特性和update語句的獨佔鎖定特性把2個表中的記錄鎖住；2所示，再次建立一個查詢時段，2條很簡單的SQL語句長時間仍沒有執行完成。

3、 檢測與排查 

3.1 通過Profile工具看死結 

Profile是SQL Server內建的跟蹤分析工具，開啟Profile來捕捉死結資訊可以更直觀的看到相關資訊。

3.2 通過系統預存程序看死結 

sp_who和sp_lock是SQL Server的2個系統預存程序，可以用它們來查詢資料庫中的鎖情況。sp_who提供有關的資料庫執行個體中目前使用者、會話和進程的資訊，如，我們看到spid=56的會話（UPDATE語句）被spid=54的會話阻塞。

sp_lock提供有關鎖的資訊，如。我們可以通過spid知道是哪個會話鎖住了資源，可以通過ObjId知道被鎖住的資源是什麼。

執行如下SQL指令碼擷取被鎖資源和資源所屬的資料庫：

SELECT OBJECT_NAME(421666738) AS LockedResource,DB_NAME(11) AS DBName;

-------------------------------------------------------------- 

LockedResource     DBName

-------------------------------------------------------------- 

tb_TE_SizeInformation   JYBGDB

執行如下指令碼擷取鎖資源的會話正在執行的SQL指令碼： 

DBCC INPUTBUFFER(54);

-------------------------------------------------------------- 

EventType     Parameters EventInfo       

--------------------------------------------------------------

Language Event  0  --根據事務的原子性實現個必要條件中 

--請求和等待 BEGIN TRAN --update語句在資料行上加獨佔鎖定 

--和其它所有鎖不相容 --實現個必要條件中的：互斥 

UPDATE tb_TE_BrandInformation SET IsCompensate=0 UPDATE tb_TE_SizeInformation SET [Description]=‘‘

4、 處理方式 

4.1 SQL Server自動處理 

“無為而治”。當資料庫產生死結時，SQL Server通過一個叫“鎖監視器”的東西捕獲死結資訊，並根據一定的規則自動選擇一個SQL作為鎖的犧牲品，並返回如下報錯

資訊： 

伺服器: 訊息 1205，層級 13，狀態 50，行 1 

事務（進程 ID  xx）與另一個進程已被死結在  lock 資源上，且該事務已被選作死結犧牲品。請重新運行該事務。

如果你對資料庫還不夠瞭解，那建議你向其他有經驗的人求助，在此之前不要輕易對資料庫進行修改。

4.2 Kill會話 

通過3.2中提到的系統預存程序可以擷取到與死結相關的資訊。可以查詢其中是哪個spid導致的死結，並使用Kill spid的方法把它幹掉。但是這隻能是一種臨時的解決方案，我們不可能一遇到死結就在使用者的生產環境裡排查死結、Kill sp。同樣的道理，也不可能一遇到死結就重啟SQL Server服務，甚至重啟資料庫伺服器。 

SQL指令碼：

Kill  54;        --此處54即分析後得到的spid值

4.3 設定鎖請求逾時 

預設情況下，資料庫沒有鎖定逾時期限。也就是說一個會話在申請新的資源時，如果這個資源已經被其它進程鎖定，那麼本會話會一直處於等待狀態。這樣無疑是有問題的。我們可以通過SQL命令來設定鎖請求逾時。也可以訪問全域變數 @@LOCK_TIMEOUT 來查看這個值。

SET LOCK_TIMEOUT  20000;     --單位是毫秒

當請求鎖超過設定時間時，SQL Server將返回錯誤。我們的程式可以根據返回的錯誤來進行響應的處理，避免長時間的使用者等待。

伺服器: 訊息 1222，層級 16，狀態 50，行 1 已超過了鎖請求逾時時段。

當然，使用這種方式來處理所有的鎖請求是不合適的，也是不負責任的。在多數情況下是我們的程式的設計、實現的問題導致了死結。在處理過程中，我們既要治標，更要治本。

4.4 修改程式 

在3.2的最後，我們通過系統預存程序和幾個命令找到了鎖定資源的SQL命令。以這次LL項目為例，我們發現是WEB管理系統上的一個統計報表（SELECT）在執行過程中長時間的那一個生產資訊表鎖定，導致現場各機台上位機系統想要插入新的生產記錄（INSERT）時間長度時間等待。所以在現場項目組每次重新啟動SQL Server服務或者重啟資料庫伺服器2個小時以後，這個問題依然重複出現。 

這個時候如果採用Kill掉這個統計報表請求的方式處理，結果和重啟SQL Server服務、重啟資料庫伺服器沒有區別，2個小時問題依舊。如果採用設定鎖請求逾時的方式處理，那麼這個統計報表每次執行都不會獲得想要的結果，而且每次執行也會鎖定一定的時間導致現場上位機的等待。 

這次我們的處理措施是：1）暫時禁用了WEB管理系統上的這個報表功能；2）重啟了SQL Server服務；3）最佳化報表的SQL語句；4）啟用報表功能。之後的一段時間沒有再次出現這樣的問題。 

通過對這個報表的效能最佳化，這個問題算是解決的差不多了。但是經過事後瞭解，發現報表的效能問題並不在於開發人員的疏忽或水平不夠。問題的根本在這個生產資訊表的設計有問題。在一個資料量達到1000w級的表中，我們採用char(10)來儲存日期值，雖然INSERT、UPDATE、DELETE時沒有問題，但是在執行SELECT且這個日期值欄位作為過濾條件時發生效能問題是必然的。經過測試，這個欄位的資料類型改為datetime時的執行時間不到效能最佳化後的10%。 

所以，不但是在開發階段，早在設計階段就已經有了效能隱患。

4.5 升級硬體 

不贅述。 

5、 如何預防 

首先要理解，在多並發的環境中死結是不可避免的，只能通過合理的資料庫設計、

良好的索引、適當的查詢語句以及隔離等級等措施盡量減少死結。 

最開始列出了死結的4個必要條件，只要想辦法破壞任意1個或多個條件就可以避免產生死結。下列方法有助於最大限度的降低死結： 

a） 按同一順序訪問對象；

b）避免事務中的使用者互動，也就是在事務執行過程中不要包含使用者互動的步驟； c）保持事務簡短並在一個批處理中； d）SELECT語句加WITH(NOLOCK)提示；

SELECT * FROM TABLE1 WITH(NOLOCK); 

SELECT * FROM TABLE2 WITH(NOLOCK);

這種寫法在執行中不對查詢到的資源加鎖，就允許2條SQL可以並發地訪問同一資源。但是加WITH(NOLOCK)提示可能會導致髒讀！！！ 

e）使用較低的隔離等級；

暫不需要瞭解，不贅述。 f）使用綁定串連； 

處理常式端的死結，非資料庫端，不贅述。

6、 結束語 

項目實施過程中遇到死結現象在所難免。通過前面的介紹，希望大家能夠對它

有一個比較簡單的認識，在遇到異常情況的時候不至於束手無策。如果以上內容有什麼技術上不對的問題或觀點，歡迎大家直接向我提出來一起研究溝通，也歡迎大家在遇到其它資料庫方面的問題時能和我一起探討，共同提高。

SQL Server死結的分析、處理與預防