如何追查 SQL Server 的 Deadlock

最近有個客戶常遇到 SQL Server 的 Deadlock 問題，查了一下，追踨 Deadlock 有兩個做法（實際應該是一樣的）：

1. 使用 SQL Server Profiler 去記錄
2. 直接下 SQL 新增追踨

以下是使用 SQL 指令追踨的方法：

1. 建立追踨 Deadlock (假定追踨檔是要存在 D:\DbLog\ 下)

    -- 運行下述語句後，每當SQL Server中發生死鎖事件，都會自動往文件e:\DbLog\deadlockdetect.trc 中插入一條記錄。
    -- 定義參數 
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5
    -- 初始化跟蹤 
    exec @rc = sp_trace_create @TraceID output, 0, N'E:\DbLog\deadlockdetect', @maxfilesize, NULL
    --    此處的e:\dblog\deadlockdetect是文件名（可自行修改），SQL會自動在後面加上.trc的擴展名 
    if (@rc != 0) goto error 
    -- 設置跟蹤事件 
    declare @on bit
    set @on = 1
    --下述語句中的148指的是locks:deadlock graph事件(參見sys.trace_events),12指的是spid列(參見    sys.trace_columns) 
    exec sp_trace_setevent @TraceID, 148, 1, @on
    exec sp_trace_setevent @TraceID, 148, 4, @on
    exec sp_trace_setevent @TraceID, 148, 11, @on
    exec sp_trace_setevent @TraceID, 148, 12, @on
    exec sp_trace_setevent @TraceID, 148, 14, @on
    exec sp_trace_setevent @TraceID, 148, 26, @on
    exec sp_trace_setevent @TraceID, 148, 41, @on
    exec sp_trace_setevent @TraceID, 148, 51, @on
    exec sp_trace_setevent @TraceID, 148, 60, @on
    exec sp_trace_setevent @TraceID, 148, 64, @on
    -- 啟動跟蹤 
    exec sp_trace_setstatus @TraceID, 1
    -- 記錄下跟蹤ID，以備後面使用 
    select TraceID = @TraceID
    goto finish 
    error: 
    select ErrorCode=@rc
    finish: 
    go
   

2. 當發生 Deadlock 後，去查看 deadlockdetect.trc 是哪些 SQL 有問題 (結果中的TextData列即以XML的形式返回死鎖的詳細信息。 )

    select fn_trace_gettable('e:\DbLog\deadlockdetect.trc', 1)
   

3. 查詢、暫停和停止服務器端跟蹤
   查詢目前有哪些 Traces 正在執行

    SELECT * FROM sys.traces
   

   暫停某個 Trace

    -- 第一個參數表示TraceID,即步驟1中的輸出參數(或是從 sys.traces 查詢得到)。第二個參數表示將狀態改為0，即暫停
    exec sp_trace_setstatus 2, 0
   

   中止某個 Trace

    -- 第一個參數表示TraceID,即步驟1中的輸出參數(或是從 sys.traces 查詢得到)。第二個參數表示將狀態改為2，即停止
    exec sp_trace_setstatus 2, 2
   

4. 在 Deadlock 的情況下，也可以直接查詢目前正在執行的 SQL

   SELECT sqltext.TEXT,req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time,
    s.host_name,
    s.login_time,
    s.login_name
   FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
    INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
   where DB_NAME(req.database_id) = '資料庫名稱'
   

​