SQL Server 死鎖快速查詢與終結：系統預存程序實戰腳本

資料庫死鎖一旦發生，必須迅速定位阻塞鏈並釋放資源。本文提供三組實用腳本：查詢鎖定資源、手動終結進程、完整的死鎖偵測與解鎖預存程序。

1. 單純查詢被鎖定的表與 SPID

SELECT request_session_id AS spid,
       OBJECT_NAME(resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

2. 依 SPID 終結進程

KILL <spid>;

3. 進階：自動偵測死鎖並輸出阻塞鏈 SQL 文本

USE master;
GO
CREATE PROCEDURE sp_who_lock AS
BEGIN
    DECLARE @spid INT, @bl INT, @intCountProperties INT, @intCounter INT;
    CREATE TABLE #tmp_lock_who (id INT IDENTITY(1,1), spid SMALLINT, bl SMALLINT);

    INSERT INTO #tmp_lock_who (spid, bl)
    SELECT 0, blocked FROM (SELECT * FROM sysprocesses WHERE blocked > 0) a
    WHERE NOT EXISTS (SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) b WHERE a.blocked = spid)
    UNION
    SELECT spid, blocked FROM sysprocesses WHERE blocked > 0;

    SELECT @intCountProperties = COUNT(*) FROM #tmp_lock_who;
    IF @intCountProperties = 0 BEGIN
        SELECT '目前無阻塞與死鎖資訊' AS message;
        DROP TABLE #tmp_lock_who; RETURN 0;
    END

    SET @intCounter = 1;
    WHILE @intCounter <= @intCountProperties BEGIN
        SELECT @spid = spid, @bl = bl FROM #tmp_lock_who WHERE id = @intCounter;
        IF @spid = 0
            SELECT '引發死鎖進程：' + CAST(@bl AS VARCHAR(10)) + '，其執行 SQL 如下';
        ELSE
            SELECT 'SPID：' + CAST(@spid AS VARCHAR(10)) + ' 被 SPID：' + CAST(@bl AS VARCHAR(10)) + ' 阻塞，當前 SQL 如下';
        DBCC INPUTBUFFER(@bl);
        SET @intCounter = @intCounter + 1;
    END
    DROP TABLE #tmp_lock_who;
    RETURN 0;
END;
GO

4. 批次終結指定資料庫所有連線

USE master;
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_killspid]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[p_killspid];
GO
CREATE PROC p_killspid @dbname VARCHAR(200) AS
DECLARE @sql NVARCHAR(500), @spid NVARCHAR(20);
DECLARE cur CURSOR FOR
    SELECT CAST(spid AS VARCHAR(20)) FROM master..sysprocesses WHERE dbid = DB_ID(@dbname);
OPEN cur;
FETCH NEXT FROM cur INTO @spid;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC('KILL ' + @spid);
    FETCH NEXT FROM cur INTO @spid;
END
CLOSE cur; DEALLOCATE cur;
GO
-- 用法：EXEC p_killspid 'YourDBName';

5. 完整鎖資源詳細檢視

CREATE TABLE #t(req_spid INT, obj_name SYSNAME);
DECLARE @s NVARCHAR(4000), @rid INT, @dbname SYSNAME, @id INT, @objname SYSNAME;
DECLARE cur CURSOR FOR
    SELECT DISTINCT req_spid, DB_NAME(rsc_dbid), rsc_objid
    FROM master..syslockinfo WHERE rsc_type IN (4,5);
OPEN cur;
FETCH NEXT FROM cur INTO @rid, @dbname, @id;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @s = 'SELECT @objname = name FROM [' + @dbname + ']..sysobjects WHERE id = @id';
    EXEC sp_executesql @s, N'@objname SYSNAME OUT, @id INT', @objname OUT, @id;
    INSERT INTO #t VALUES (@rid, @objname);
    FETCH NEXT FROM cur INTO @rid, @dbname, @id;
END
CLOSE cur; DEALLOCATE cur;

SELECT 進程id = a.req_spid,
       資料庫 = DB_NAME(rsc_dbid),
       類型 = CASE rsc_type
           WHEN 1 THEN 'NULL' WHEN 2 THEN '資料庫' WHEN 3 THEN '檔案'
           WHEN 4 THEN '索引' WHEN 5 THEN '資料表' WHEN 6 THEN '頁面'
           WHEN 7 THEN '鍵' WHEN 8 THEN 'Extent' WHEN 9 THEN 'RID'
           WHEN 10 THEN '應用程式' END,
       物件id = rsc_objid,
       物件名 = b.obj_name,
       rsc_indid
FROM master..syslockinfo a LEFT JOIN #t b ON a.req_spid = b.req_spid;
DROP TABLE #t;

提醒：強制 KILL 進程可能導致交易回滾耗時，優先嘗試在應用端正常結束連線；若必須介入，建議先用 sp_who_lock 確認阻塞鏈再決定。