用于更新/插入组合的Postgres锁定

Question

我有两张桌子。一种是日志表，另一种实质上包含只能使用一次的优惠券代码。

用户需要能够赎回优惠券，优惠券将插入日志表中的一行并将优惠券标记为使用的优惠券(通过将used列更新为true)。

当然，这里有一个明显的种族条件/安全问题。

在过去的mySQL世界里，我也做过类似的事情。在那个世界里，我会在全局上锁定两个表，确保逻辑安全，知道每次只能发生一次这样的事情，然后在我完成之后解锁这些表。

在Postgres有更好的方法来做这件事吗？特别是，我担心锁是全局的，但不一定非得是- I，只需要确保没有其他人试图输入特定的代码，所以也许某些行级别的锁定可以工作吗？

Answer 1

我以前听说过类似于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只在一个事务中成功。UPDATE在更新之前获取每个目标行的行级锁。如果一个并发事务尝试在同一行UPDATE，它将看到该行上的锁，并等待阻塞事务完成(ROLLBACK或COMMIT)，然后是锁队列中的第一个：

• 如果已提交，请重新检查条件。如果仍然是NOT used，则锁定该行并继续进行。否则，UPDATE现在找不到符合条件的行，什么也不做，不返回任何行，所以INSERT也什么也不做。
• 如果回滚，锁定行并继续。

没有比赛条件的潜力。

除非您在同一事务中添加更多的写操作，或者以其他方式锁定更多的行，否则死锁是没有潜力的。

INSERT是免费的.如果由于某些错误，coupon_id已经在log表中(而且您在log.coupon_id上有一个唯一的或PK约束)，那么整个事务将在唯一违规之后回滚。会表明你的数据库中存在非法状态。如果上述语句是写入log表的唯一方法，则不应该发生这种情况。

Answer 2

欧文的回答是最先进的。但为了完整起见，我想提出更多的选择。

悲观锁

第一种是典型的--在优惠券行上使用悲观锁，必须在开始时采取：

select * from coupon where id=? and used=false for update
insert into log(...)

第二笔交易将等到第一笔交易完成。如果第一次TX提交，那么select将不会返回优惠券，您可以中止。这种方法类似于Erwin的答案，但允许将事务拆分为多个语句。

带有跳过锁的

悲观锁

如果你不在乎买哪一张优惠券--任何一张能满足价格的优惠券--那你就可以跳过锁上的优惠券，继续前进。在这种情况下，您永远不必中止事务--它们总是会成功的：

select * from coupon where used=false for update skip locked limit 1
insert into log(...)

我对这种“悲观的锁”犹豫不决，因为实际上它永远不会被阻止。

可序列化隔离

另一个选项是乐观的(对于PG)：使用可序列化的隔离。在这种情况下，可以在更新used=false之前插入日志表。请注意，当第二个TX尝试提交时，它会注意到第一个TX更新了它更新的行(或者即使它只读取它)，您将得到：

ERROR:  could not serialize access due to concurrent update

更多关于PG中的隔离。