SQL Server死鎖處理

死鎖原理

根據操作系統中的定義：死鎖是指在一組進程中的各個進程均占有不會釋放的資源，但因互相申請被其他進程所站用不會釋放的資源而處于的一種永久等待狀態。

死鎖的四個必要條件：

互斥條件(Mutual exclusion)：資源不能被共享，只能由一個進程使用。

請求與保持條件(Hold and wait)：已經得到資源的進程可以再次申請新的資源。

非剝奪條件(No pre-emption)：已經分配的資源不能從相應的進程中被強制地剝奪。

循環等待條件(Circular wait)：系統中若干進程組成環路，該環路中每個進程都在等待相鄰進程正占用的資源。

對應到SQL Server中，當在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定的資源，此時會造成這些任務永久阻塞，從而出現死鎖；這些資源可能是：單行(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(PAG，8KB)、區結構(EXT，連續的8頁)、堆或B樹(HOBT)?、表(TAB，包括數據和索引)、文件(File，數據庫文件)、應用程序專用資源(APP)、元數據(METADATA)、分配單元(Allocation_Unit)、整個數據庫(DB)。一個死鎖示例如下圖所示：

說明：T1、T2表示兩個任務；R1和R2表示兩個資源；由資源指向任務的箭頭(如R1->T1，R2->T2)表示該資源被改任務所持有；由任務指向資源的箭頭(如T1->S2，T2->S1)表示該任務正在請求對應目標資源；

其滿足上面死鎖的四個必要條件：

(1).互斥：資源S1和S2不能被共享，同一時間只能由一個任務使用；

(2).請求與保持條件：T1持有S1的同時，請求S2；T2持有S2的同時請求S1；

(3).非剝奪條件：T1無法從T2上剝奪S2，T2也無法從T1上剝奪S1；

(4).循環等待條件：上圖中的箭頭構成環路，存在循環等待。

死鎖排查

(1).?使用SQL Server的系統存儲過程sp_who和sp_lock，可以查看當前數據庫中的鎖情況；進而根據objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪個資源被鎖，用dbcc ld(@blk)，可以查看最后一條發生給SQL Server的Sql語句；

(2).?使用?SQL Server Profiler?分析死鎖:?將?Deadlock graph?事件類添加到跟蹤。此事件類使用死鎖涉及到的進程和對象的?XML?數據填充跟蹤中的?TextData?數據列。SQL Server?事件探查器?可以將?XML?文檔提取到死鎖?XML (.xdl)?文件中，以后可在?SQL Server Management Studio?中查看該文件。

避免死鎖

上面1中列出了死鎖的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死鎖發生，一般有以下幾種方法(FROM Sql Server 2005聯機叢書)：

(1).按同一順序訪問對象。(注：避免出現循環)

(2).避免事務中的用戶交互。(注：減少持有資源的時間，較少鎖競爭)

(3).保持事務簡短并處于一個批處理中。(注：同(2)，減少持有資源的時間)

(4).使用較低的隔離級別。(注：使用較低的隔離級別（例如已提交讀）比使用較高的隔離級別（例如可序列化）持有共享鎖的時間更短，減少鎖競爭)

(5).使用基于行版本控制的隔離級別：2005中支持快照事務隔離和指定READ_COMMITTED隔離級別的事務使用行版本控制，可以將讀與寫操作之間發生的死鎖幾率降至最低：

SET ALLOW_SNAPSHOT_ISOLATION ON --事務可以指定?SNAPSHOT?事務隔離級別;

SET READ_COMMITTED_SNAPSHOT ON??--指定?READ_COMMITTED?隔離級別的事務將使用行版本控制而不是鎖定。默認情況下(沒有開啟此選項，沒有加with nolock提示)，SELECT語句會對請求的資源加S鎖(共享鎖)；而開啟了此選項后，SELECT不會對請求的資源加S鎖。

注意：設置?READ_COMMITTED_SNAPSHOT?選項時，數據庫中只允許存在執行?ALTER DATABASE?命令的連接。在?ALTER DATABASE?完成之前，數據庫中決不能有其他打開的連接。數據庫不必一定要處于單用戶模式中。

(6).使用綁定連接。(注：綁定會話有利于在同一臺服務器上的多個會話之間協調操作。綁定會話允許一個或多個會話共享相同的事務和鎖(但每個回話保留其自己的事務隔離級別)，并可以使用同一數據，而不會有鎖沖突。可以從同一個應用程序內的多個會話中創建綁定會話，也可以從包含不同會話的多個應用程序中創建綁定會話。在一個會話中開啟事務(begin tran)后，調用exec sp_getbindtoken @Token out;來取得Token，然后傳入另一個會話并執行EXEC sp_bindsession @Token來進行綁定(最后的示例中演示了綁定連接)。

死鎖處理方法：

(1).?根據2中提供的sql，查看那個spid處于wait狀態，然后用kill spid來干掉(即破壞死鎖的第四個必要條件:循環等待)；當然這只是一種臨時解決方案，我們總不能在遇到死鎖就在用戶的生產環境上排查死鎖、Kill sp，我們應該考慮如何去避免死鎖。

(2).?使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設定鎖請求超時。默認情況下，數據庫沒有超時期限(timeout_period值為-1，可以用SELECT @@LOCK_TIMEOUT來查看該值，即無限期等待)。當請求鎖超過timeout_period時，將返回錯誤。timeout_period值為0時表示根本不等待，一遇到鎖就返回消息。設置鎖請求超時，破環了死鎖的第二個必要條件(請求與保持條件)。

服務器:?消息?1222，級別?16，狀態?50，行?1

已超過了鎖請求超時時段。

(3). SQL Server內部有一個鎖監視器線程執行死鎖檢查，鎖監視器對特定線程啟動死鎖搜索時，會標識線程正在等待的資源；然后查找特定資源的所有者，并遞歸地繼續執行對那些線程的死鎖搜索，直到找到一個構成死鎖條件的循環。檢測到死鎖后，數據庫引擎?選擇運行回滾開銷最小的事務的會話作為死鎖犧牲品，返回1205?錯誤，回滾死鎖犧牲品的事務并釋放該事務持有的所有鎖，使其他線程的事務可以請求資源并繼續運行。

兩個死鎖示例及解決方法

5.1 SQL死鎖

(1).?測試用的基礎數據：

CREATE?TABLE?Lock1(C1?int?default(0));

CREATE?TABLE?Lock2(C1?int?default(0));

INSERT?INTO?Lock1?VALUES(1);

INSERT?INTO?Lock2?VALUES(1);

(2).?開兩個查詢窗口，分別執行下面兩段sql

--Query?1

Begin?Tran

Update?Lock1?Set?C1=C1+1;

WaitFor?Delay?'00:01:00';

SELECT?*?FROM?Lock2

Rollback?Tran;

--Query?2

Begin?Tran

Update?Lock2?Set?C1=C1+1;

WaitFor?Delay?'00:01:00';

SELECT?*?FROM?Lock1

Rollback?Tran;

上面的SQL中有一句WaitFor Delay '00:01:00'，用于等待1分鐘，以方便查看鎖的情況。

SQL SQL Server

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。