PostgreSQL 有時無法檢測到序列化失敗

我有一個應用程序語言的 http-server，大致如下所示：

item = SELECT item FROM table WHERE field = 'value'
if (item) {
 UPDATE another field on item
} else {
 INSERT item VALUE field = 'value' and so on
}

該欄位具有唯一約束。大約每隔幾分鐘就會有兩個相同的請求同時value進入。他們都執行相同的程式碼，所以當然存在競爭條件。我決定如果我將它包裝在一個具有 SERIALIZABLE 隔離級別的事務中並在每次序列化失敗時重試，那麼問題就解決了。在本地主機上它工作得很好。我確實看到瞭如預期的錯誤程式碼 40001 引發的序列化失敗。

但是，由於某種原因，在負載下的數據庫中的生產中Unique constraint violation偶爾會拋出程式碼 23505。它只是有時發生。我付出了很多努力在 localhost 上重現它，但失敗了。每次我得到一個正常的 40001。我嘗試在單獨的程序中執行伺服器程式碼，並在不同的點放置延遲以強制執行某些執行順序。它只發生在生產中。所以我在 prod 中添加了廣泛的日誌記錄。日誌說，每次出現問題時，一item開始都沒有發現，但當 INSERT 發生時，它就會觸發Unique constraint violation。所以它看起來只是一個競爭條件。

但是為什麼有時在某些情況下 Postgres SERIALIZABLE 事務沒有按應有的方式檢測到它呢？或者如何調試？在幾十個案例中，它一天會發生好幾次。

（幾乎）準確的 SQL 如下所示：（我省略了詳細的欄位列表和準確的參數）

START TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM products
WHERE product_code = $1
LIMIT 1 -- product_code is a parameter

SELECT *
FROM licenses
WHERE license_key = $1
LIMIT 1 -- license_key is another and it's unique across the table
-- At this point SELECT did not return anything but it was inserted before this INSERT by exactly the same transaction

INSERT INTO licenses ("license_key", "product_id", "other_data",...)
VALUES ($1, $2, $3,...) RETURNING "id"
query failed: error: duplicate key value violates unique constraint "licenses_license_key_key"

除非存在錯誤，否則在序列化事務中執行此操作應該可行。

作為一種解決方法，請使用此命令，它會嘗試添加新許可證；如果許可證密鑰已經存在，它將新小時添加到現有值：

INSERT INTO licenses(license_key, product_id, hours)
VALUES ($1,
       (SELECT product_id FROM products WHERE product_code = $2 LIMIT 1),
       $3)
ON CONFLICT (license_key) DO UPDATE
SET hours = hours + EXCLUDED.hours
RETURNING id;

文件

此評論/答案由 Thomas Munro 發送給我：

我的猜測是它們可能有多個唯一索引，並且可能遇到了這個問題。

總結： 過去我們總是在檢測到 SSI 故障之前報告唯一約束違規 (UCV)，其想法是錯誤無關緊要；SSI 只對送出的事務做出承諾。然後我們添加了一行調整，使其在引發 UCV 之前嘗試檢測 SSI 故障（將其視為錯誤優先級：恕我直言，SSI 錯誤需要更高的優先級，因為自動事務重試系統需要可靠的 SSI 錯誤；它們是什麼？應該與UCV有關嗎？）。但現在我們知道這還不夠好。如該執行緒中所述，在報告 UCV 之前，我們可能需要四處奔波並找到 所有在我們報告 UCV 之前，您將插入的索引讓他們有機會檢查 SSI 錯誤。在該 -bugs 執行緒中報告的情況下，您可以通過更改定義 UNIQUE 約束的順序來更改結果。啊。這需要對架構進行一些重新設計，可能包括一個新的索引 AM 條目功能，但我認為這是值得的（如果做得好，它還可以允許 SSI 錯誤比排除約束錯誤具有更高的優先級）。

引用自：https://dba.stackexchange.com/questions/270328