您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

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

編輯：關於SqlServer

功能概覽

`p_lockinfo` 可：

偵測**阻塞鏈** (Blocker → Victims)
顯示 SPID、KPID、資料庫、登入、主機、程式、等待類型、開啟交易數
透過 `DBCC INPUTBUFFER` 取得正在執行的 SQL 批次
選項：`@kill_lock_spid=1` 自動 `KILL` 阻塞者 (Bloker)；`@show_spid_if_nolock=1` 無阻塞時顯示所有作用中 SPID

完整程式碼 (已修正語法、加入註解)

CREATE PROCEDURE dbo.p_lockinfo
    @kill_lock_spid      bit = 0,  -- 1=KILL 阻塞者, 0=僅顯示
    @show_spid_if_nolock bit = 0   -- 1=無阻塞時顯示所有 SPID
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 偵測阻塞鏈：Bloker (blocked=0 但被其他 SPID blocked) + Victims (blocked<>0)
    SELECT 
        ID = IDENTITY(int,1,1),
        Flag = CASE WHEN a.blocked = 0 THEN N'阻塞者(Bloker)' ELSE N'|_受害者(Victim)>' END,
        SPID = a.spid,
        KPID = a.kpid,
        BlockedBy = a.blocked,
        DBID = a.dbid,
        DBName = DB_NAME(a.dbid),
        UID = a.uid,
        LoginName = a.loginame,
        CPU = a.cpu,
        LoginTime = a.login_time,
        OpenTran = a.open_tran,
        Status = a.status,
        HostName = a.hostname,
        ProgramName = a.program_name,
        HostProcess = a.hostprocess,
        NTDomain = a.nt_domain,
        NetAddress = a.net_address,
        Sort1 = CASE WHEN a.blocked = 0 THEN a.spid ELSE a.blocked END,
        Sort2 = CASE WHEN a.blocked = 0 THEN 0 ELSE 1 END
    INTO #LockChain
    FROM master..sysprocesses a
    JOIN (
        SELECT blocked FROM master..sysprocesses WHERE blocked <> 0 GROUP BY blocked
    ) b ON a.spid = b.blocked
    WHERE a.blocked = 0

    UNION ALL

    SELECT 
        ID = IDENTITY(int,1,1),
        Flag = N'|_受害者(Victim)>',
        SPID = a.spid, KPID = a.kpid, BlockedBy = a.blocked,
        DBID = a.dbid, DBName = DB_NAME(a.dbid), UID = a.uid,
        LoginName = a.loginame, CPU = a.cpu, LoginTime = a.login_time,
        OpenTran = a.open_tran, Status = a.status, HostName = a.hostname,
        ProgramName = a.program_name, HostProcess = a.hostprocess,
        NTDomain = a.nt_domain, NetAddress = a.net_address,
        Sort1 = a.blocked, Sort2 = 1
    FROM master..sysprocesses a
    WHERE a.blocked <> 0

    ORDER BY Sort1, Sort2;

    DECLARE @Count int = @@ROWCOUNT, @i int = 1;

    -- 2. 若無阻塞且要求顯示所有 SPID
    IF @Count = 0 AND @show_spid_if_nolock = 1
    BEGIN
        INSERT #LockChain (Flag, SPID, KPID, BlockedBy, DBID, DBName, UID, LoginName,
                           CPU, LoginTime, OpenTran, Status, HostName, ProgramName,
                           HostProcess, NTDomain, NetAddress)
        SELECT N'正常程序', 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

    -- 3. 取得 INPUTBUFFER 並選擇性 KILL
    IF @Count > 0
    BEGIN
        CREATE TABLE #InputBuffer (ID int IDENTITY(1,1), EventType nvarchar(30), Parameters nvarchar(4000), EventInfo nvarchar(max));

        IF @kill_lock_spid = 1
        BEGIN
            DECLARE @SPID varchar(10), @Flag nvarchar(20);
            WHILE @i <= @Count
            BEGIN
                SELECT @SPID = CAST(SPID AS varchar(10)), @Flag = Flag FROM #LockChain WHERE ID = @i;
                INSERT #InputBuffer EXEC ('DBCC INPUTBUFFER(' + @SPID + ') WITH NO_INFOMSGS');
                IF @Flag = N'阻塞者(Bloker)' EXEC ('KILL ' + @SPID);
                SET @i += 1;
            END
        END
        ELSE
        BEGIN
            WHILE @i <= @Count
            BEGIN
                DECLARE @Sql nvarchar(200);
                SELECT @Sql = 'DBCC INPUTBUFFER(' + CAST(SPID AS varchar(10)) + ') WITH NO_INFOMSGS' FROM #LockChain WHERE ID = @i;
                INSERT #InputBuffer EXEC (@Sql);
                SET @i += 1;
            END
        END

        -- 4. 輸出結果
        SELECT 
            lc.Flag, lc.SPID, lc.KPID, lc.BlockedBy, lc.DBName, lc.LoginName,
            lc.CPU, lc.LoginTime, lc.OpenTran, lc.Status, lc.HostName,
            lc.ProgramName, lc.HostProcess, lc.NTDomain, lc.NetAddress,
            ib.EventInfo AS [執行的SQL批次]
        FROM #LockChain lc
        JOIN #InputBuffer ib ON lc.ID = ib.ID
        ORDER BY lc.ID;
    END
END;

使用範例

-- 僅偵測顯示
EXEC dbo.p_lockinfo 0, 1;

-- 偵測並自動 KILL 阻塞者 (謹慎使用！)
EXEC dbo.p_lockinfo 1, 0;

⚠️ 重要警示

自動 KILL 極高風險：阻塞者可能正持有關鍵交易鎖、執行長時間維護作業、或為系統程序。KILL 會觸發大規模回滾 (可能耗時數小時)、日誌暴增、資料庫進入恢復模式。**生產環境嚴禁 `@kill_lock_spid=1`**，僅供測試環境或極端情況人工確認後使用。
`sysprocesses` 為相容性檢視，SQL Server 2005+ 建議改用 `sys.dm_exec_requests` + `sys.dm_exec_sessions` + `sys.dm_exec_sql_text` 取得更完整資訊 (等待類型、資源、查詢計畫控制代碼)。
死鎖 (Deadlock) ≠ 阻塞。死鎖由鎖監視器自動偵測並選擇犧牲者 (Error 1205)。本程序偵測的是**活性阻塞**。

現代化替代方案

Extended Events `xml_deadlock_report`**：自動捕捉死鎖圖 (XML)，含受害者、鎖資源、執行計畫。
Query Store / `sys.dm_exec_query_stats`**：找出高頻阻塞查詢，優化索引/隔離等級/交易長度。
自動化監控腳本 (PowerShell/dbatools)**：定期掃描 `sys.dm_os_waiting_tasks`、`sys.dm_tran_locks`，記錄阻塞歷史、告警。