並發事務導致競爭條件對插入具有唯一約束

我有一個 Web 服務（http api），它允許使用者輕鬆地創建資源。在身份驗證和驗證之後，我將數據傳遞給 Postgres 函式，並允許它檢查授權並在數據庫中創建記錄。

我今天發現了一個錯誤，當兩個 http 請求在同一秒內發出時，導致該函式被呼叫兩次，並使用相同的數據。函式內部有一個子句，它在表上進行選擇以查看值是否存在，如果確實存在，則獲取 ID 並在下一次操作中使用它，如果不存在，則插入數據，獲取返回 ID，然後在下一個操作中使用它。下面是一個簡單的例子。

select id into articleId from articles where title = 'my new blog';
if articleId is null then
   insert into articles (title, content) values (_title, _content)
   returning id into articleId;
end if;
-- Continue, using articleId to represent the article for next operations...

正如您可能猜到的那樣，我對兩個事務都進入if articleId is null then塊並試圖插入表的數據進行了幻讀。一個成功了，另一個因為一個領域的獨特限製而爆炸。

我已經查看瞭如何防禦這種情況並找到了一些不同的選擇，但由於某些原因，它們似乎都不符合我們的需求，我正在努力尋找任何替代方案。

1. insert ... on conflict do nothing/update...我首先查看了on conflict看起來不錯的選項，但是唯一的選項是do nothing它不會返回導致衝突的記錄的 ID，並且do update不會工作，因為它會導致觸發器在實際數據中被觸發沒有改變。在某些情況下，這不是問題，但在許多情況下，這可能會使會話使用者會話無效，這是我們無法做到的。
2. set transaction isolation level serializable;這似乎是最吸引人的答案，但是即使我們的測試套件也可能導致讀/寫依賴關係，就像上面一樣，如果某些東西不存在，我們想要插入，如果存在則返回它並繼續進行進一步的操作。如果我們有幾個執行上述程式碼的待處理事務，它將導致Postgres 文件的事務 ISO 中所述的讀/寫依賴錯誤。

這種並發讀/寫事務應該如何處理？

我自己和我的團隊都沒有聲稱自己是數據庫專家，更不用說 Postgres 專家了，但我覺得這一定是一個已解決的問題，或者過去有人遇到過。我們願意接受任何建議。如果上面提供的資訊還不夠，請發表評論，我會根據需要添加更多資訊。

嘗試第insert一個，使用on conflict ... do nothingand returning id。如果該值已經存在，您將不會從該語句中得到任何結果，因此您必須執行 aselect來獲取 ID。

如果兩個事務嘗試同時執行此操作，其中一個將阻塞insert（因為數據庫還不知道另一個事務是否會送出或回滾），並且只有在另一個事務完成後才能繼續。

問題的根源在於，在預設READ COMMITTED隔離級別下，每個並發 UPSERT（或任何查詢，就此而言）只能看到在查詢開始時可見的行。手冊：

當事務使用此隔離級別時，SELECT查詢（不帶FOR UPDATE/SHARE子句）只能看到在查詢開始之前送出的數據；它永遠不會看到未送出的數據或併發事務在查詢執行期間送出的更改。

但是UNIQUE索引是絕對的，仍然必須考慮同時輸入的行——即使是不可見的行。因此，您可以獲得唯一違規的異常，但您仍然看**不到同一查詢中的衝突行。手冊：

INSERT由於另一個事務的結果，其影響對快照不可見，因此帶有ON CONFLICT DO NOTHING子句的插入可能不會繼續進行。INSERT同樣，這僅適用於已送出讀模式。

這個問題的強力“解決方案”是用ON CONFLICT ... DO UPDATE. 然後，新行版本在同一查詢中可見。但是有幾個副作用，我建議不要這樣做。其中之一是UPDATE觸發器被觸發——這是你想要明確避免的事情。與 SO 密切相關的答案：

• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

剩下的選項是啟動一個新命令（在同一個事務中），然後它可以從上一個查詢中*看到這些衝突的行。*現有的兩個答案都表明了這一點。再看說明書：

但是，SELECT確實會看到在其自己的事務中執行的先前更新的影響，即使它們尚未送出。另請注意，如果其他事務在第一次啟動SELECT之後和第二次啟動之前送出更改，則兩個連續命令可以看到不同的數據，即使它們在單個事務中也是如此。SELECT``SELECT

但你想要更多：

– 繼續，使用 articleId 來表示文章以進行下一步操作…

如果並發寫入操作可能能夠更改或刪除行，那麼絕對可以肯定，您還必須鎖定所選行。（無論如何，插入的行都被鎖定。）

而且由於您似乎有非常有競爭力的交易，為了確保您成功，循環直到成功。包裝成一個 plpgsql 函式：

CREATE OR REPLACE FUNCTION f_articleid(_title text, _content text, OUT _articleid int)
 LANGUAGE plpgsql AS
$func$
BEGIN
  LOOP
     SELECT articleid
     FROM   articles
     WHERE  title = _title
     FOR    UPDATE          -- or maybe a weaker lock 
     INTO   _articleid;

     EXIT WHEN FOUND;

     INSERT INTO articles AS a (title, content)
     VALUES (_title, _content)
     ON     CONFLICT (title) DO NOTHING  -- (new?) _content is discarded
     RETURNING a.articleid
     INTO   _articleid;

     EXIT WHEN FOUND;
  END LOOP;
END
$func$;

詳細解釋：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

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