// PostgreSQL 錯誤參考

PostgreSQL 40001:無法序列化存取

在 REPEATABLE READ 或 SERIALIZABLE 隔離層級下,有另一筆並行交易變更了這筆交易所依賴的資料。PostgreSQL 已將其回復;請重試整筆交易。

SQLSTATE
40001
名稱
serialization_failure
類別
交易復原
錯誤訊息
could not serialize access due to concurrent update

它的意義

PostgreSQL 較高的隔離層級承諾交易會看到一致的快照。當另一筆交易提交了衝突的變更、使這個承諾無法維持時,PostgreSQL 會以 40001 中止其中一筆交易,而不是回傳一個在序列順序下不可能出現的結果。

訊息會根據原因而有所不同: could not serialize access due to concurrent update 或 concurrent delete (你嘗試修改的資料列,在你的快照之後被別人改掉了),以及 could not serialize access due to read/write dependencies among transactions (SERIALIZABLE 偵測到危險模式)。解法相同:從頭重跑一次交易。

常見原因

  • 二 REPEATABLE READ 或 SERIALIZABLE 交易更新同一個資料列;第二個提交的會失敗。
  • A SERIALIZABLE 交易讀取的資料列隨後被另一個交易變更,形成可能產生異常的模式。
  • 由多個並行可序列化交易所更新的熱門資料列,例如計數器或帳戶餘額。

如何修正

  1. 重試。程式碼若使用 REPEATABLE READ 或 SERIALIZABLE 必須捕捉 SQLSTATE 40001 並以短暫退避重試數次,每次都重新執行整個交易,包括其中的讀取。

  2. 可序列化交易保持簡短,並盡量少碰資料列。

  3. 如果這項工作不需要可序列化,預設 READ COMMITTED 層級會在更新時重新讀取最新的資料列版本,而不是直接失敗。

    BEGIN ISOLATION LEVEL READ COMMITTED;
    UPDATE counters SET n = n + 1 WHERE name = 'visits';
    COMMIT;
  4. 在 SERIALIZABLE,就把交易如實宣告為唯讀; SERIALIZABLE READ ONLY DEFERRABLE 永遠不會因序列化錯誤而失敗。

    BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

安全地重現問題

在臨時資料庫上使用兩個獨立連線執行。

-- Session 1:
CREATE TABLE acct (id int PRIMARY KEY, bal int);
INSERT INTO acct VALUES (1, 100);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT bal FROM acct WHERE id = 1;
-- Session 2:
UPDATE acct SET bal = 50 WHERE id = 1;
-- Session 1:
UPDATE acct SET bal = bal - 10 WHERE id = 1;  -- ERROR: could not serialize access due to concurrent update
ROLLBACK;

相關錯誤

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

// 錯誤更少,修復更快

在 SQLly 裡操作 PostgreSQL

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