Postgresql

鎖定 Postgres 以進行 UPDATE / INSERT 組合

  • January 5, 2022

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

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

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

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

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

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

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

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

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