SQL Server 死鎖進程偵測與自動處理預存程序：p_lockinfo 完整代碼解析

死鎖發生時需快速識別受害者與加害者並決定是否自動 KILL。本文提供部署於 master 的 p_lockinfo 預存程序，支援參數控制「是否自動殺死死鎖進程」與「無死鎖時是否顯示正常進程」，並輸出每個 SPID 正在執行的 SQL 文本，實戰極佳。

核心邏輯

1. 從 sysprocesses 找出 阻塞鏈頭 (blocked = 0 但有其他 SPID 的 blocked 指向它) → 標記「死鎖的進程」。
2. 找出所有 blocked > 0 的進程 → 標記「|_犧牲品_>」。
3. 按阻塞鏈頭與層級排序，輸出完整阻塞鏈。
4. 若 @kill_lock_spid = 1，對標記為「死鎖的進程」執行 KILL；否則僅顯示。
5. 透過 DBCC INPUTBUFFER 取得每個 SPID 當前執行語句。

完整建立腳本

USE master; GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_lockinfo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[p_lockinfo]; GO

CREATE PROC p_lockinfo
  @kill_lock_spid BIT = 1,      -- 1=自動KILL死鎖進程, 0=僅顯示
  @show_spid_if_nolock BIT = 1  -- 1=無死鎖時顯示正常進程, 0=不顯示
AS
BEGIN
  DECLARE @count INT, @i INT = 1, @spid VARCHAR(10), @tag VARCHAR(20), @s NVARCHAR(1000);

  -- 建立阻塞鏈暫存表
  SELECT ID = IDENTITY(INT,1,1), 標志, 進程ID=spid, 線程ID=kpid, 塊進程ID=blocked,
         資料庫ID=dbid, 資料庫名=DB_NAME(dbid), 用戶ID=uid, 用戶名=loginame,
         累計CPU=cpu, 登陸時間=login_time, 開啟交易=open_tran, 進程狀態=status,
         工作站=hostname, 應用程式=program_name, 主機進程=hostprocess,
         域名=nt_domain, 網卡=net_address
  INTO #t FROM (
    SELECT 標志='死鎖的進程', spid, kpid, a.blocked, dbid, uid, loginame, cpu, login_time, open_tran,
           status, hostname, program_name, hostprocess, nt_domain, net_address,
           s1=a.spid, s2=0
    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
    SELECT '|_犧牲品_>', spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
           status, hostname, program_name, hostprocess, nt_domain, net_address,
           s1=blocked, s2=1
    FROM master..sysprocesses WHERE blocked <> 0
  ) a ORDER BY s1, s2;

  SELECT @count = @@ROWCOUNT;

  IF @count = 0 AND @show_spid_if_nolock = 1
  BEGIN
    INSERT #t
    SELECT 標志='正常的進程', spid, kpid, blocked, dbid, DB_NAME(dbid), uid, loginame, cpu, login_time,
           open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
    FROM master..sysprocesses;
    SET @count = @@ROWCOUNT;
  END

  IF @count > 0
  BEGIN
    CREATE TABLE #t1 (id INT IDENTITY(1,1), a NVARCHAR(30), b INT, EventInfo NVARCHAR(255));

    IF @kill_lock_spid = 1
    BEGIN
      WHILE @i <= @count
      BEGIN
        SELECT @spid = 進程ID, @tag = 標志 FROM #t WHERE id = @i;
        INSERT #t1 EXEC ('DBCC INPUTBUFFER(' + @spid + ')');
        IF @tag = '死鎖的進程' EXEC ('KILL ' + @spid);
        SET @i = @i + 1;
      END
    END
    ELSE
    BEGIN
      WHILE @i <= @count
      BEGIN
        SELECT @s = 'DBCC INPUTBUFFER(' + CAST(進程ID AS VARCHAR) + ')' FROM #t WHERE id = @i;
        INSERT #t1 EXEC (@s);
        SET @i = @i + 1;
      END
    END

    SELECT a.*, 進程的SQL語句 = b.EventInfo
    FROM #t a JOIN #t1 b ON a.id = b.id;
  END
END; GO

使用範例

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

-- 自動 KILL 死鎖進程並顯示全部
EXEC p_lockinfo @kill_lock_spid = 1, @show_spid_if_nolock = 1;

補充：純查詢版死鎖檢視 (不建立物件)

SELECT 標志, 進程ID=spid, 線程ID=kpid, 塊進程ID=blocked, 資料庫ID=dbid,
       資料庫名=DB_NAME(dbid), 用戶ID=uid, 用戶名=loginame, 累計CPU=cpu,
       登陸時間=login_time, 開啟交易=open_tran, 進程狀態=status,
       工作站=hostname, 應用程式=program_name, 主機進程=hostprocess,
       域名=nt_domain, 網卡=net_address
FROM (
  SELECT 標志='死鎖的進程', spid, kpid, a.blocked, dbid, uid, loginame, cpu, login_time, open_tran,
         status, hostname, program_name, hostprocess, nt_domain, net_address,
         s1=a.spid, s2=0
  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
  SELECT '|_犧牲品_>', spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
         status, hostname, program_name, hostprocess, nt_domain, net_address,
         s1=blocked, s2=1
  FROM master..sysprocesses WHERE blocked <> 0
) a ORDER BY s1, s2;