萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 死鎖根因分析與六大預防策略:統一順序、小事務、低隔離、綁定連接

死鎖本質:循環等待

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。

六大預防原則(按優先級)

  1. 統一存取順序:所有事務按固定順序鎖資源(如先表 A 後表 B),斷開循環等待鏈。
  2. 避免事務中用戶互動:思考時間 = 鎖持有時間 ↑ → 衝突率 ↑。批次處理、異步化。
  3. 事務極小化、單批次:減少網路來回、鎖持有窗口。大量刪改分批(如每 5000 行 COMMIT)。
  4. 降低隔離等級:預設 Read Committed → 開啟 RCSI (READ_COMMITTED_SNAPSHOT) 讀不阻寫;必要時才用 Snapshot/Serializable。
  5. 善用索引精準定位:WHERE 走索引 → 鎖行數 ↓ → 衝突 ↓。避免掃描鎖升級為表鎖。
  6. 綁定連接:同應用多連線共享鎖 (sp_getbindtoken/sp_bindsession),避免自家死鎖。

額外實務建議

  • 高頻表加叢集索引;
  • 避免大範圍 UPDATE/DELETE,分批處理;
  • 只讀查詢加 WITH(NOLOCK) 或啟用 RCSI;
  • 啟用 死鎖追蹤旗標 1222/1204 記錄死鎖圖至日志:DBCC TRACEON(1222, -1)。
copyright © 萬盛學電腦網 all rights reserved