鎖定 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
,鎖定該行並繼續。否則UPDATE
now 找不到符合條件的行並且什麼都不做,不返回任何行,所以 theINSERT
也什麼都不做。- 如果回滾,則鎖定該行並繼續。
沒有潛在的競爭條件。
除非您將更多的寫入放入同一個事務中,否則不會發生死鎖,或者以其他方式鎖定更多的行而不是僅僅鎖定一個。
INSERT
是無憂無慮的。如果由於某些錯誤coupon_id
已經在log
表中(並且您對 UNIQUE 或 PK 約束log.coupon_id
),則整個事務將在唯一違規後回滾。將指示您的數據庫中的非法狀態。如果上面的語句是寫入log
表的唯一方法,那永遠不會發生。