淺談偵測「死結(DeadLock)」的作法，以使用「追蹤旗標(Trace flag)」1204、1222為例

若是使用 SSMS 管理工具，在遭遇「死結」時，系統將顯示以下的錯誤訊息：

訊息 1205，層級 13，狀態 51，行 2
交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

請參考下圖所示：




死結偵測由鎖定監視器執行緒所執行，它會定期在 Database Engine 執行個體的所有工作中啟動搜尋。

下列幾點描述搜尋程序：
預設間隔是 5 秒。
如果鎖定監視器執行緒發現死結，死結偵測間隔會從 5 秒降低，最低降至 100 毫秒，視死結頻率而定。
如果鎖定監視器執行緒停止尋找死結，Database Engine 會增加搜尋間隔為 5 秒。

如果剛偵測到死結，則會假設後續還有必須等待鎖定的執行緒進入死結循環。
在偵測到死結之後，前面幾個鎖定等待會立即觸發死結搜尋，而不需等到下個死結偵測間隔。

例如，如果目前間隔是 5 秒，並且剛偵測到死結，則下個鎖定等待會立即啟動死結偵測設定。如果這個鎖定等待是死結的一部分，則會立即偵測到它，而不需等到下個死結搜尋期間。

但是若在用戶端應用程式上，若沒有設計處理這類問題的功能，僅是接收到錯誤號碼 1025，但將無從得知為何會發生「死結」。

若要偵測「死結」，可以使用數種方式，例如：使用 SQL Profiler 等，請參考下圖所示：


但使用 SQL Profiler 這類的工具，這表示說必須要事先開啟執行，否則將無法錄製到相關的資訊。

---

以下將使用「追蹤旗標(trace flag)」作法，將發生「死結」的相關資料，寫入到 SQL Server 記錄檔內作存放。

/*
追蹤旗標 (Trace flag)

追蹤旗標 1204 和追蹤旗標 1222
發生死結時，追蹤旗標 1204 和追蹤旗標 1222 會傳回在 SQL Server 2005 錯誤記錄檔中擷取到的資訊。

追蹤旗標 1204 報告死結所涉及的每一個節點格式化的死結資訊。
追蹤旗標 1222 先按處理序再按資源來格式化死結資訊。

可同時啟用兩種追蹤旗標來取得相同死結事件的兩種表示法。

追蹤旗標 3605 可以將記錄到的結果寫到SQL Server 錯誤記錄檔。

但是蹤旗標 1204 和追蹤旗標 1222 ，已經會將其輸出到 SQL Server 2005 錯誤記錄檔中。
所以，可以省略使用追蹤旗標 3605。
*/
USE master
GO
-- 啟用追蹤旗標 1204、1222。
DBCC TRACEON (1204, 1222,-1)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

在啟用追蹤旗標後，若是遭遇到「死結」，則在 SQL Server 記錄檔，可以看到以下的紀錄，請參考下圖所示：

---

若需要在 SQL Server 啟動時，一併啟用追蹤旗標來記錄「死結」，可以使用 -T 選項來啟動 SQL Server。
具有 -T 選項的追蹤旗標時，請使用大寫 "T" 來傳送追蹤旗標號碼。

但去調整 SQL Server 的啟動，也可能會發生遇到管理人員忘了有這件事的案例。
或許可以改用，使用「作業(Job)」，在 SQL Agent Service 啟動時，一併啟用追蹤旗標 1204、1222。

---

-- 停用指定的追蹤旗標 1204、1222。
DBCC TRACEOFF(1204, 1222,-1)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

---

若是要建立「作業(Job)」於 SQL Server Agent 服務啟動，一併使用「追蹤旗標」來偵測「死結」，請參考以下的範例：

USE [msdb]
GO
--01 建立「作業」
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'使用「追蹤旗標」來偵測「死結」')
EXEC msdb.dbo.sp_delete_job @job_name=N'使用「追蹤旗標」來偵測「死結」', @delete_unused_schedule=1
GO
EXEC msdb.dbo.sp_add_job @job_name=N'使用「追蹤旗標」來偵測「死結」',
@enabled=1,
@owner_login_name=N'sa'

--02 建立「步驟」
EXEC msdb.dbo.sp_add_jobstep
@job_name =  N'使用「追蹤旗標」來偵測「死結」',
@step_name=N'stp01_使用「追蹤旗標(Trace flag)」1204、1222',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- 啟用追蹤旗標 1204、1222。
DBCC TRACEON (1204, 1222,-1)
GO  ',
@database_name=N'master'

--03 設定排程
EXEC msdb.dbo.sp_add_jobschedule
@job_name =  N'使用「追蹤旗標」來偵測「死結」',
@name=N'sch_SQLServerAgent啟動時，自動啟動',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100330,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959

--04 指派此「作業」給本機伺服器使用。若未指定，此「作業」將以「多伺服器作業」方式存在
EXEC msdb.dbo.sp_add_jobserver @job_name =  N'使用「追蹤旗標」來偵測「死結」' ;
GO

---

參考資料：
偵測與結束死結
http://msdn.microsoft.com/zh-tw/library/ms178104.aspx

使用 SQL Server 服務啟動選項
http://msdn.microsoft.com/zh-tw/library/ms190737.aspx

SQL Server 技術公告-如何解決死結
http://support.microsoft.com/?kbid=832524

處理 SQL Server 中的死結情況的描述
http://support.microsoft.com/kb/118552/zh-tw