| 現象 | 阻塞 | 死鎖 |
|---|---|---|
| 定義 | 連線 A 持有資源未釋放，連線 B 等待該資源 | 連線 A 持有資源 1 等資源 2，連線 B 持有資源 2 等資源 1，互相等待 |
| 資料庫處理 | 無法自動解除，需等待或人工干預 | 自動選擇犧牲者 (victim) 終止交易，另一連線繼續 |
| 典型場景 | 長交易未 COMMIT/ROLLBACK | 多連線不同順序存取相同資源 |
單一用戶端執行緒開兩條連線非同步查詢，主執行緒處理連線 A 結果時阻塞，導致連線 B 結果無法送達，雙方陷入等待。
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 PROCEDURE p_lockinfo
@kill_lock_spid BIT = 0, -- 1=殺除死鎖進程, 0=僅顯示
@show_spid_if_nolock BIT = 1 -- 無死鎖時是否顯示正常進程
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count INT, @s NVARCHAR(1000), @i INT;
-- 擷取死鎖鏈：持有鎖且未被阻塞的進程 (死鎖進程) + 被阻塞進程 (犧牲品)
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, 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, @i = 1;
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
DECLARE @spid VARCHAR(10), @標志 VARCHAR(10);
WHILE @i <= @count
BEGIN
SELECT @spid = 進程ID, @標志 = 標志 FROM #t WHERE id = @i;
INSERT #t1 EXEC('DBCC INPUTBUFFER(' + @spid + ')');
IF @@ROWCOUNT = 0 INSERT #t1(a) VALUES(NULL);
IF @標志 = '死鎖的進程' 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);
IF @@ROWCOUNT = 0 INSERT #t1(a) VALUES(NULL);
SET @i = @i + 1;
END
END
SELECT a.*, 進程的SQL語句 = b.EventInfo
FROM #t a JOIN #t1 b ON a.id = b.id
ORDER BY 進程ID;
END
SET NOCOUNT OFF;
END
GO
-- 僅查看死鎖與正常進程
EXEC p_lockinfo;
-- 查看並自動殺除死鎖進程
EXEC p_lockinfo @kill_lock_spid = 1, @show_spid_if_nolock = 0;
DBCC INPUTBUFFER 取得該 SPID 最後一批 T-SQLsysprocesses 系統檢視master 資料庫建立，且呼叫者需 sysadmin 權限 (KILL 需要)。@kill_lock_spid=0 觀察確認後再啟用。sys.dm_exec_requests、sys.dm_tran_locks、Extended Events 進行更精細的阻塞/死鎖分析。
