SQL Server 死鎖偵測與自動處理預存程序 p_lockinfo

功能概覽

建立於 master 資料庫的預存程序 p_lockinfo，可：

• 識別死鎖鏈（阻塞者與受害者）
• 顯示涉及 SPID 的輸入緩衝區（正在執行的 SQL）
• 可選參數自動 KILL 死鎖進程
• 無死鎖時可選擇顯示所有正常進程

完整程式碼

USE master;
GO
IF OBJECT_ID('dbo.p_lockinfo', 'P') IS NOT NULL DROP PROCEDURE dbo.p_lockinfo;
GO

CREATE PROCEDURE p_lockinfo
    @kill_lock_spid bit = 1,          -- 1=殺掉死鎖進程, 0=僅顯示
    @show_spid_if_nolock bit = 1      -- 1=無死鎖時顯示正常進程, 0=不顯示
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count int, @i int = 1, @spid varchar(10), @flag varchar(20);

    -- 1. 收集死鎖鏈：阻塞者 + 被阻塞者
    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, 主機進程ID = hostprocess,
           網域 = nt_domain, 網卡位址 = net_address
    INTO #t
    FROM (
        SELECT 標旗 = '死鎖的進程',
               spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
               status, hostname, program_name, hostprocess, nt_domain, net_address,
               s1 = 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;

    -- 2. 無死鎖時依參數決定是否顯示正常進程
    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;
        SELECT @count = @@ROWCOUNT;
    END

    -- 3. 輸出結果並可選 KILL
    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, @flag = 標旗 FROM #t WHERE id = @i;
                INSERT #t1 EXEC ('DBCC INPUTBUFFER(' + @spid + ')');
                IF @flag = '死鎖的進程' EXEC ('KILL ' + @spid);
                SET @i = @i + 1;
            END
        END
        ELSE
        BEGIN
            WHILE @i <= @count
            BEGIN
                DECLARE @s nvarchar(100) = 'DBCC INPUTBUFFER(' + CAST((SELECT 進程ID FROM #t WHERE id = @i) AS varchar) + ')';
                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

使用範例

-- 自動殺掉死鎖並顯示細節
EXEC p_lockinfo;

-- 僅顯示死鎖資訊，不殺進程
EXEC p_lockinfo @kill_lock_spid = 0;

-- 無死鎖時也不顯示正常進程
EXEC p_lockinfo @show_spid_if_nolock = 0;

補充說明

• 適用 SQL Server 2000/2005/2008/R2（sysprocesses 相容檢視）。
• 新版建議改用 sys.dm_exec_requests、sys.dm_os_waiting_tasks 及 Extended Events 進行死鎖監控。
• KILL 操作會回滾未完成交易，請謹慎在生產環境啟用自動殺除。