T1 鎖 A 等 B,T2 鎖 B 等 A → 互相阻塞 → 引擎選犧牲者回滾 (Error 1205)。
CREATE PROCEDURE sp_who_lock AS
BEGIN
DECLARE @spid SMALLINT, @bl SMALLINT, @cnt INT, @i INT = 1;
CREATE TABLE #tmp (id INT IDENTITY(1,1), spid SMALLINT, bl SMALLINT);
INSERT #tmp(spid, bl)
SELECT 0, blocked FROM sysprocesses WHERE blocked > 0 AND blocked NOT IN (SELECT spid FROM sysprocesses WHERE blocked > 0)
UNION SELECT spid, blocked FROM sysprocesses WHERE blocked > 0;
SELECT @cnt = COUNT(*) FROM #tmp;
IF @cnt = 0 BEGIN PRINT '無阻塞/死鎖'; RETURN; END
WHILE @i <= @cnt BEGIN
SELECT @spid = spid, @bl = bl FROM #tmp WHERE id = @i;
IF @spid = 0
PRINT '死鎖源頭 SPID: ' + CAST(@bl AS VARCHAR) + ',其 SQL:';
ELSE
PRINT 'SPID ' + CAST(@spid AS VARCHAR) + ' 被 SPID ' + CAST(@bl AS VARCHAR) + ' 阻塞,SQL:';
DBCC INPUTBUFFER(@bl);
SET @i += 1;
END
DROP TABLE #tmp;
END;
執行 EXEC sp_who_lock 即可定位阻塞鏈與問題 SQL。
sp_getbindtoken/sp_bindsession),避免自家死鎖。WITH(NOLOCK) 或啟用 RCSI;DBCC TRACEON(1222, -1)。