鎖定 Postgres 以進行 UPDATE / INSERT 組合

我有兩張桌子。一個是日誌表；另一個本質上包含只能使用一次的優惠券程式碼。

使用者需要能夠兌換優惠券，這將在日誌表中插入一行並將優惠券標記為已使用（通過將used列更新為true）。

自然，這裡有一個明顯的競爭條件/安全問題。

我過去在 mySQL 領域做過類似的事情。在那個世界裡，我會全域鎖定兩個表，在知道這一次只能發生一次的情況下確保邏輯安全，然後在完成後解鎖表。

Postgres 有沒有更好的方法來做到這一點？特別是，我擔心鎖是全域的，但不一定是——我真的只需要確保沒有其他人試圖輸入那個特定的程式碼，所以也許一些行級鎖定會起作用？

我以前在 MySQL 中聽說過類似的並發問題。在 Postgres 中並非如此。

READ COMMITTED預設事務隔離級別中的內置行級鎖就足夠了。

我建議使用帶有數據修改 CTE 的單個語句（MySQL 也沒有），因為將值從一個表直接傳遞到另一個表很方便（如果你需要的話）。如果您不需要coupon表中的任何內容，您也可以使用帶有單獨UPDATE和INSERT語句的事務。

WITH upd AS (
  UPDATE coupon
  SET    used = true
  WHERE  coupon_id = 123
  AND    NOT used
  RETURNING coupon_id, other_column
  )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

多筆交易試圖兌換同一張優惠券應該是一件*罕見的事情。*他們有一個唯一的號碼，不是嗎？然而，在同一時間嘗試不止一項交易應該要少得多。（可能是應用程序錯誤或有人試圖玩弄系統？）

儘管如此，無論如何，只有一筆UPDATE交易成功。An在更新之前獲取每個目標行上的行級鎖。如果並發事務嘗試訪問同一行，它將看到該行上的鎖並等待阻塞事務完成（或），然後成為鎖隊列中的第一個：UPDATE``UPDATE``ROLLBACK``COMMIT

• 如果已送出，請重新檢查條件。如果它仍然是NOT used，鎖定該行並繼續。否則UPDATEnow 找不到符合條件的行並且什麼都不做，不返回任何行，所以 theINSERT也什麼都不做。
• 如果回滾，則鎖定該行並繼續。

沒有潛在的競爭條件。

除非您將更多的寫入放入同一個事務中，否則不會發生死鎖，或者以其他方式鎖定更多的行而不是僅僅鎖定一個。

INSERT是無憂無慮的。如果由於某些錯誤coupon_id已經在log表中（並且您對 UNIQUE 或 PK 約束log.coupon_id），則整個事務將在唯一違規後回滾。將指示您的數據庫中的非法狀態。如果上面的語句是寫入log表的唯一方法，那永遠不會發生。

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