postgresql - 正确的 postgres 锁以防止重复插入

Question

问题说明

考虑一个 rewards 表，它有一个 type 列，其中一个可能的值是 ONE_PER_PERSON。

还有一个redeemed_rewards 链接表，用于跟踪哪些用户兑换了哪些奖励。它有两列:user_id 和 reward_id。

现在考虑负责兑换奖励的业务逻辑的高级函数。该函数的签名如下所示:

function redeemReward(userId, rewardId)

具体来说，考虑兑换 ONE_PER_PERSON 奖励的情况。在较高层次上，此案例的函数逻辑如下所示:

1. 开始交易

2. 查询以确保该用户之前未兑换过奖励。也就是说，确保以下查询返回计数 0:

   SELECT COUNT(*) FROM redeemed_rewards 
   WHERE user_id = ${userId} AND reward_id = ${rewardId}
   

3. 假设还没有，插入兑换的奖励:

   INSERT INTO redeemed_rewards VALUES (${userId}, ${rewardId})
   

4. 提交交易

此逻辑的问题在于它容易受到竞争条件的影响。由于该函数理论上可以被多个线程调用，因此可以想象，2 个线程都可以绕过第 2 步，每个线程都在另一个线程到达第 4 步之前，导致插入了两条记录，从而违反了 ONE_PER_PERSON 约束.

问题

我认为正确的解决方案是在第 1 步锁定表以防止插入，并保持锁定状态直到第 4 步。

我的问题是:

1. 适合这种情况的 postgres 锁是什么？理想情况下，我可以锁定仅包含指定 userId 的插入，以便其他用户不受影响。但是，我认为这是不可能的。那么我应该使用哪种类型的表锁？
2. 奖励问题:如何在 knex 查询中实现此锁定？文档似乎没有提到任何关于锁的内容......

请注意

唯一索引对我来说不是一个可行的解决方案。并非所有类型的奖励都是每个用户一个。另外，我特别想了解 postgres 锁定以及如何在需要的情况下正确使用它。

Answer 1

这带来了一个问题，因为如果没有某种显式锁定，重复插入有点难以防止。

第一个选项是使用奖励 ID 或用户作为信号量，以有效地将您的所有写入序列化到特定用户或奖励 ID。这样你就可以做类似的事情:

SELECT * FROM reward WHERE id = ? FOR UPDATE;

然后，每次兑换相同奖励的尝试都将等待其他尝试先通过。您可以根据您的流量方法对用户执行相同的操作。然后当事务完成时，锁被释放。

这是通过锁定交易奖励中的行来实现的。这里的主要优点是它很简单。主要缺点是只有您的应用程序才具备以这种方式序列化读取的能力。

通过这种方式，许多人可以一次兑换不同的奖励 id，但对于每个奖励 id，它将锁定奖励表中的行并等待阻止其他人执行相同的操作，直到它提交。

第二种方法是使用咨询锁。这里有一些优点，但也有一些缺点，所以如果行锁不能满足您的需要，我会看看(并花一些时间阅读文档)。

编辑:

实际上，唯一索引是可能的，但要做到这一点，您必须稍微重新考虑一下您的数据库。正如您提到的，(user_id, reward_id) 组合对于某些奖励类型是唯一的。

所以您需要做的是在奖励表上为 reward_id, reward_type 创建一个唯一索引，然后将 reward_type 添加到 redeemed_rewards 表中的外键。

CREATE UNIQUE INDEX redeemed_rewards_only_one_per_user 
    ON redeemed_rewards (user_id, reward_id) 
 WHERE reward_type = 'ONE_PER_USER`

那么当只有这些类型的奖励被多次兑换时，PostgreSQL 会抛出一个错误。这具有让数据库直接执行逻辑的优势，因此您不必担心会因杂散查询或管理操作而搞砸事情。