// SQL Server 錯誤參考

SQL Server 錯誤 1205:發生死結,並被選為犧牲者

兩個工作階段各自等待對方持有的鎖定。SQL Server 已回復這筆交易,讓另一個能完成。請重試。

錯誤號碼
1205
嚴重性
13
類別
交易與鎖定
錯誤訊息
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

它的意義

死結是鎖等待形成的循環:工作階段 A 持有 B 需要的鎖,B 也持有 A 需要的鎖。SQL Server 的死結監視器會偵測到循環,挑一個犧牲者收場:回復該工作階段的整個交易並拋出 1205。犧牲者是最低 DEADLOCK_PRIORITY,優先順序相同時,則挑回復成本最低的那個。

另一個工作階段就像什麼事都沒發生一樣繼續運作。死結在併發環境下是正常現象;目標是讓它很少發生,發生時就重試。

常見原因

  • 兩段程式碼路徑以相反順序更新同一批資料表或資料列。
  • 在預設的讀取認可(read committed)隔離層級下,讀取方在等某個寫入方,而那個寫入方又在等讀取方的共用鎖(透過非叢集索引查詢時很常見)。
  • 缺少索引,導致更新與刪除掃描並鎖定的資料列遠多於實際變更的資料列。
  • 長時間的交易,或使用者在開啟的交易中途進行互動。
  • 較高的隔離層級,例如 SERIALIZABLE 持有範圍鎖。

如何修正

  1. 當交易因 1205 錯誤失敗時,在應用程式中重試交易。在 T-SQL 中,重試迴圈如下所示:

    DECLARE @tries int = 0;
    WHILE @tries < 3
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1;
            UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2;
            COMMIT;
            BREAK;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW;
            SET @tries += 1;
            WAITFOR DELAY '00:00:00.100';
        END CATCH
    END;
  2. 查看死結圖。內建的 system_health 擴充事件工作階段會記錄每個死結。

    SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph
    FROM (SELECT CAST(target_data AS xml) AS target_data
          FROM sys.dm_xe_session_targets AS t
          JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
          WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS data
    CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed);
  3. 在每個交易中以相同順序存取資料表和資料列,並讓交易保持簡短。

  4. 新增索引,使寫入只影響它們變更的資料列;死結圖顯示每個鎖定位於哪個索引上。

  5. 考慮採用讀取認可快照隔離,讓讀取作業使用資料列版本而非共用鎖定,不再與寫入作業發生死結。

    ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  6. 把較不重要的工作標記為優先犧牲品。

    SET DEADLOCK_PRIORITY LOW;

安全地重現問題

在兩個獨立連線上,依所示順序對臨時資料庫執行這些步驟,然後回滾存活的那個工作階段,並刪除資料表。

-- Setup (once):
CREATE TABLE dbo.DL (id int PRIMARY KEY, v int);
INSERT INTO dbo.DL VALUES (1, 0), (2, 0);
-- Session 1:
BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2;
UPDATE dbo.DL SET v = 2 WHERE id = 1;  -- waits
-- Session 1:
UPDATE dbo.DL SET v = 1 WHERE id = 2;  -- one session gets Msg 1205

問題

死結之後我的資料會遺失嗎?

受害那一方的整個交易會被回滾,它的任何變更因此都沒有存入。其他資料不受影響。請重新執行該交易。

NOLOCK 可以解決死結問題嗎?

它可以阻止讀取端取得共用鎖,但會允許髒讀和不一致讀(遺漏或重複的資料列)。讀取認可快照隔離能消除讀寫互相阻塞,又不會有這些風險。

相關錯誤

更多交易和鎖定錯誤

程式碼和訊息文字來自 Microsoft 資料庫引擎事件和錯誤參考。瀏覽完整的 SQL Server 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 SQL Server

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 SQL Server 和你使用的其他資料庫。