正确的Postgres锁定以防止重复插入

问题的解释

考虑具有type列的rewards表，其可能值为ONE_PER_PERSON。

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

现在考虑一个高级功能，负责赎回奖励的业务逻辑。该函数的签名如下：

function redeemReward(userId, rewardId)

具体来说，请考虑正在兑换ONE_PER_PERSON奖励的情况。在高级，这种情况的函数逻辑看起来像：

开始交易
查询以确保该用户以前没有兑换奖励。也就是说，确保以下查询返回计数0：
```
SELECT COUNT(*) FROM redeemed_rewards 
WHERE user_id = ${userId} AND reward_id = ${rewardId}
```

假设没有，插入赎回的奖励：

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

这种逻辑的问题是它很容易受到种族条件的影响。由于理论上可以通过多个线程调用该函数，因此可以想象，两个线程都可以绕过步骤2，每个线程都可以绕过步骤4，从而产生了两个插入的记录，因此违反了ONE_PER_PERSON约束。

。

我认为正确的解决方案是在步骤1的插入物中锁定表格，并将其锁定直至步骤4。

我的问题是：

在这种情况下，适当的邮政锁定是什么？理想情况下，我可以锁定仅包含指定用户ID的插入物，以使其他用户不受影响。但是，我认为这是不可能的。那么我应该使用哪种类型的表锁？
奖励问题：如何在KNEX查询中暗示此锁定？文档似乎没有提及有关锁的任何内容...

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

这出现了一个问题，因为如果没有某种明确的锁定，重复的插入物很难预防。

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

SELECT * FROM reward WHERE id = ? FOR UPDATE;

然后，每次赎回相同奖励的尝试都将等待其他尝试首先尝试的尝试。您可以使用用户进行同样的操作，而取决于您的流量方法。然后，当交易完成后，锁定锁。

这可以通过锁定行奖励交易来起作用。这里的主要优点是它很简单。主要的缺点是，仅涵盖您的应用程序才能以这种方式序列化读取的能力。

以这种方式，许多人可以一次兑现不同的奖励ID，但是对于每个奖励ID，它将锁定在奖励表中，并等待其他人执行此操作。

第二种方法是使用咨询锁。这里有一些优点，但也有一些缺点，所以如果行锁不会做您需要的事情，我会考虑一下（并花一些时间在文档上）。

编辑：

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

因此，您需要做的是在reward_id, reward_type的奖励表上创建一个唯一的索引，然后在您的Exemeemed_Rewards表中添加reward_type。

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

然后，当仅对这些类型的奖励兑换不止一次时，PostgreSQL将丢失错误。这具有DB直接执行逻辑的优势，因此您不必担心用流浪查询或管理操作弄乱事情。