SQL Server 死鎖監控與自動處理存儲過程：p_lockinfo 完整碼解析

功能概覽

存儲過程 p_lockinfo 可查詢當前阻塞鏈、識別死鎖受害者，並可選擇自動 Kill 死鎖進程。

參數說明

核心邏輯流程

1. 查詢 master..sysprocesses，透過 blocked 欄位建立阻塞鏈：
   blocked=0 且被其他 SPID 阻塞 → 死鎖持有者 (標記 '死鎖的進程')
   blocked<>0 → 受害者 (標記 '|_犧牲品_>')
2. 若無死鎖且 @show_spid_if_nolock=1，補查所有正常進程。
3. 對每個 SPID 執行 DBCC INPUTBUFFER(spid) 取得正在執行的 SQL 文字。
4. 若 @kill_lock_spid=1，對死鎖持有者發送 KILL spid。

關鍵 SQL 片段解析

-- 1. 找出死鎖持有者 (被阻塞但自己不阻塞別人)
SELECT ... FROM master..sysprocesses a
JOIN (SELECT blocked FROM master..sysprocesses GROUP BY blocked) b
  ON a.spid = b.blocked
WHERE a.blocked = 0

UNION ALL

-- 2. 找出受害者 (正在被阻塞)
SELECT ... FROM master..sysprocesses WHERE blocked <> 0

輸出欄位

• 標識 (死鎖的進程 / 犧牲品 / 正常的進程)
• SPID、KPID、Blocked、DBID、DB名、用戶、CPU、登入時間、開啟事務數、狀態、工作站、程式名、Host Process、網域、網卡地址
• EventInfo (正在執行的 SQL 片段)

使用範例

-- 僅檢視死鎖
EXEC p_lockinfo @kill_lock_spid=0, @show_spid_if_nolock=0;

-- 自動殺掉死鎖並顯示所有進程
EXEC p_lockinfo @kill_lock_spid=1, @show_spid_if_nolock=1;

注意事項與改進建議

• sysprocesses 為舊版相容視圖，SQL Server 2005+ 建議改用 sys.dm_exec_requests、sys.dm_exec_sessions、sys.dm_os_waiting_tasks。
• DBCC INPUTBUFFER 僅回傳前 255 字元，長 SQL 會截斷；建議改用 sys.dm_exec_sql_text(sql_handle)。
• 自動 Kill 具風險：可能誤殺正常長時間查詢，建議改為告警通知 DBA 人工介入。
• 死鎖資訊更完整來源：sys.dm_tran_locks、sys.dm_os_waiting_tasks、擴展事件 xml_deadlock_report。