// PostgreSQL 錯誤參考

PostgreSQL 40P01:偵測到死結

有兩筆以上的交易互相等待對方的鎖定。PostgreSQL 已回復其中一筆,讓另一筆能完成;請重試失敗的那一筆。

SQLSTATE
40P01
名稱
deadlock_detected
類別
交易復原
錯誤訊息
deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.

它的意義

死結是鎖等待形成的循環:交易 A 持有 B 需要的鎖,同時又在等 B 持有的鎖。雙方都動不了,因此當鎖等待持續 deadlock_timeout (預設 1 秒)之後,PostgreSQL 會檢查是否形成循環等待;若找到,就以 40P01 中止其中一個交易。

被中止的交易會完全回滾。另一個交易則繼續執行。 DETAIL 這幾行會顯示涉及哪些處理程序,以及 CONTEXT 哪一行語句正在等待。

常見原因

  • 兩筆交易以不同順序更新同一批資料列,例如一筆把錢從帳戶 1 轉到帳戶 2,另一筆從帳戶 2 轉到帳戶 1。
  • 外鍵:插入子資料列需要一個 FOR KEY SHARE 鎖定父資料列,這可能與另一個正在更新該父列的交易發生衝突。
  • 觸及大量資料列的長時間交易,會放大鎖定順序重疊的時間窗。
  • 明確 LOCK TABLE 或 SELECT ... FOR UPDATE 在不同的程式碼路徑中會以不同順序取得。

如何修正

  1. 重試失敗的交易。死結是正常的並行處理結果,應用程式應該捕捉 SQLSTATE 40P01 (以及 40001)並重新執行整個交易。

  2. 在任何地方都以一致的順序上鎖,例如總是先鎖住較低的 ID。

    BEGIN;
    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  3. 保持交易簡短:先做讀取和運算,再執行寫入,並儘早提交。

  4. 開啟 log_lock_waits 所以等待時間較長的 deadlock_timeout 會連同相關陳述式一併記錄,接著再去翻伺服器記錄檔。

    ALTER SYSTEM SET log_lock_waits = on;
    SELECT pg_reload_conf();

安全地重現問題

在臨時資料庫上,使用兩個獨立連線依序執行這些步驟。

-- Session 1:
CREATE TABLE dl (id int PRIMARY KEY, v int);
INSERT INTO dl VALUES (1, 0), (2, 0);
BEGIN; UPDATE dl SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN; UPDATE dl SET v = 2 WHERE id = 2;
UPDATE dl SET v = 2 WHERE id = 1;   -- waits
-- Session 1:
UPDATE dl SET v = 1 WHERE id = 2;   -- one session gets ERROR: deadlock detected

問題

發生死結時,PostgreSQL 會中止哪個交易?

死結檢查找出循環的那一個,一般來說無法預測。撰寫每個交易時,都要讓它可以重試。

相關錯誤

程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 PostgreSQL

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