死鎖是並發系統不可避免的現象,但可透過設計規範、監控偵測、程式碼優化將發生率降至最低並快速恢復。
資料庫鎖機制天然滿足前三條,**打破循環等待**是預防核心。
-- Session 1 -- Session 2
BEGIN TRAN BEGIN TRAN
UPDATE TableA SET ... WHERE Id=1 UPDATE TableB SET ... WHERE Id=2
-- 持有 TableA X 鎖 -- 持有 TableB X 鎖
UPDATE TableB SET ... WHERE Id=2 UPDATE TableA SET ... WHERE Id=1
-- 等待 TableB X 鎖 (被 S2 阻塞) -- 等待 TableA X 鎖 (被 S1 阻塞)
-- ★ 死鎖發生 ★
-- 擷取死鎖圖 (XML)
SELECT CAST(target_data AS XML)
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer';
DBCC TRACEON (1222, -1); -- 1222: XML 格式輸出至錯誤日誌
DBCC TRACEON (1204, -1); -- 1204: 文字格式
CREATE PROCEDURE dbo.sp_WhoLock
AS
BEGIN
SET NOCOUNT ON;
DECLARE @spid SMALLINT, @bl SMALLINT, @cnt INT, @i INT = 1;
CREATE TABLE #Blockers (Id INT IDENTITY, Spid SMALLINT, Blocker SMALLINT);
-- 找出阻塞鏈頭 (被阻塞但不阻塞他人)
INSERT INTO #Blockers (Spid, Blocker)
SELECT 0, blocked FROM sys.sysprocesses WHERE blocked > 0
EXCEPT
SELECT blocked, 0 FROM sys.sysprocesses WHERE blocked > 0;
-- 所有被阻塞 SPID
INSERT INTO #Blockers (Spid, Blocker)
SELECT spid, blocked FROM sys.sysprocesses WHERE blocked > 0;
SELECT @cnt = COUNT(*) FROM #Blockers;
IF @cnt = 0 BEGIN PRINT '無阻塞/死鎖'; RETURN; END
WHILE @i <= @cnt
BEGIN
SELECT @spid = Spid, @bl = Blocker FROM #Blockers WHERE Id = @i;
IF @spid = 0
PRINT '死鎖鏈頭/根阻塞者 SPID: ' + CAST(@bl AS VARCHAR) + ' 其輸入緩衝:';
ELSE
PRINT 'SPID ' + CAST(@spid AS VARCHAR) + ' 被 SPID ' + CAST(@bl AS VARCHAR) + ' 阻塞,輸入緩衝:';
DBCC INPUTBUFFER(@bl);
SET @i += 1;
END
DROP TABLE #Blockers;
END;
DEADLOCK_PRIORITY (LOW/NORMAL/HIGH) 與 **撤銷成本** (Log 量) 選擇-- C# 範例
int retry = 0, maxRetry = 3;
while (true) {
try {
using (var txn = conn.BeginTransaction()) {
// ... 業務邏輯
txn.Commit();
break;
}
} catch (SqlException ex) when (ex.Number == 1205 && retry < maxRetry) {
retry++;
Thread.Sleep((int)Math.Pow(2, retry) * 100 + new Random().Next(100));
}
}
victim-process:被選中的犧牲者process-list/process:參與執行緒,含 inputbuf (SQL 文本)、executionStack/frame (執行堆疊)resource-list/keylock/pagelock/objectlock:爭用資源,含 owner-list/owner、waiter-list/waitermode (S/U/X/IS/IU/IX/SIU)、waittype、lasttranstarted死鎖不可全除,但可透過**統一存取順序、縮短交易、索引支援、RCSI 隔離、應用層重試**構建韌性系統。