SQL Server 錯誤 1205:發生死結,並被選為犧牲者
兩個工作階段各自等待對方持有的鎖定。SQL Server 已回復這筆交易,讓另一個能完成。請重試。
- 錯誤號碼
1205- 嚴重性
- 13
- 類別
- 交易與鎖定
它的意義
死結是鎖等待形成的循環:工作階段 A 持有 B 需要的鎖,B 也持有 A 需要的鎖。SQL Server 的死結監視器會偵測到循環,挑一個犧牲者收場:回復該工作階段的整個交易並拋出 1205。犧牲者是最低 DEADLOCK_PRIORITY,優先順序相同時,則挑回復成本最低的那個。
另一個工作階段就像什麼事都沒發生一樣繼續運作。死結在併發環境下是正常現象;目標是讓它很少發生,發生時就重試。
常見原因
- 兩段程式碼路徑以相反順序更新同一批資料表或資料列。
- 在預設的讀取認可(read committed)隔離層級下,讀取方在等某個寫入方,而那個寫入方又在等讀取方的共用鎖(透過非叢集索引查詢時很常見)。
- 缺少索引,導致更新與刪除掃描並鎖定的資料列遠多於實際變更的資料列。
- 長時間的交易,或使用者在開啟的交易中途進行互動。
- 較高的隔離層級,例如
SERIALIZABLE持有範圍鎖。
如何修正
-
當交易因 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; -
查看死結圖。內建的
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); -
在每個交易中以相同順序存取資料表和資料列,並讓交易保持簡短。
-
新增索引,使寫入只影響它們變更的資料列;死結圖顯示每個鎖定位於哪個索引上。
-
考慮採用讀取認可快照隔離,讓讀取作業使用資料列版本而非共用鎖定,不再與寫入作業發生死結。
ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-
把較不重要的工作標記為優先犧牲品。
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 可以解決死結問題嗎?
它可以阻止讀取端取得共用鎖,但會允許髒讀和不一致讀(遺漏或重複的資料列)。讀取認可快照隔離能消除讀寫互相阻塞,又不會有這些風險。
相關錯誤
1222鎖定請求超出逾時期限3960快照隔離更新衝突266EXECUTE 後交易計數不一致40P01PostgreSQL:偵測到死結1213MySQL:嘗試取得鎖定時偵測到死結ORA-00060Oracle:等待資源時偵測到死結
更多交易和鎖定錯誤
3930交易無法認可3902COMMIT 沒有對應的 BEGIN3903ROLLBACK 沒有對應的 BEGIN3609交易在觸發器中結束3616觸發器執行期間引發錯誤6401找不到交易或儲存點3998無法提交的交易已回復3621陳述式已遭終止
程式碼和訊息文字來自 Microsoft 資料庫引擎事件和錯誤參考。瀏覽完整的 SQL Server 錯誤清單 或 搜尋每個資料庫錯誤.
在 SQLly 裡操作 SQL Server
適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 SQL Server 和你使用的其他資料庫。