Proper postgrace locking to prevent duplicate inserts

Question

Explanation of the problem

Consider a rewards table that has a type column, one of whose possible values ​​is ONE_PER_PERSON .

There is also a redeemed_rewards link redeemed_rewards to track which rewards have been redeemed by users. It has two columns: user_id and reward_id .

Now consider the high-level function responsible for the business logic that will redeem rewards. The function signature looks like this:

 function redeemReward(userId, rewardId) 

In particular, we consider the case when a reward a ONE_PER_PERSON redeemed. At a high level, the logic of functions for this case looks like this:

• Start transaction

• A request to ensure that the reward has not previously been redeemed by this user. That is, make sure that the following query returns the number 0:

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

• Assuming this is not the case, insert the redeemed reward:

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

• Commit transaction

The problem with this logic is that it is vulnerable to race conditions. Since it is theoretically theoretically possible to name several threads, it can be assumed that 2 threads can bypass step 2, each before the other reaches step 4, which will lead to two inserted records and thereby violate the ONE_PER_PERSON restriction.

Question

I think the correct solution is to lock the table from the inserts in step 1 and keep it locked until step 4.

My questions:

• What is the appropriate postgres lock for this situation? Ideally, I could block inserts containing only the specified userId so that other users do not suffer. However, I do not think this is possible. So what type of table lock should I use?
• Bonus question: how can this lock be implemented in a knut query? The docs don't seem to say anything about locks ...

NOTE

A unique index is not a viable solution for me. Not all types of rewards are one for each user. In addition, I specifically want to understand postgres locking and how to use it correctly in situations where it is necessary.

Answer 1

This presents a problem, since duplicate inserts are a little difficult to prevent without any explicit blocking.

The first option would be to use a reward or user identifier as a semaphore to efficiently serialize all your records to a specific user or reward identifier. This way you do something like:

 SELECT * FROM reward WHERE id = ? FOR UPDATE; 

Then each attempt to redeem the same reward will wait for other attempts to pass the first. You can do the same with the user, depending on your approach to traffic. Then, when the transaction completes, the lock is released.

This works by blocking rows in a transaction reward. The main advantage here is that it is simple. The main disadvantage is that only your application is covered by the ability to serialize reading in this way.

Thus, many people can reuse different reward identifiers at once, but for each reward identifier, it blocks a row in the reward table and expects others to do the same until it does.

The second approach is to use advisory locks. There are some advantages here, but there are some disadvantages, so I would look at this (and spend some time on the documentation) if line locks will not do what you need.

Edit:

A unique index is actually possible, but for this you need to rethink your database a bit. As you mentioned, the combination (user_id, reward_id) unique to certain types of rewards.

So, you need to create a unique index in the reward table for reward_id, reward_type , and then add rewards for your foreign key in the redeemed_rewards table to your archived code.

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

PostgreSQL then throws an error when only these types of rewards are redeemed more than once. This has the advantage that the logic is directly applied in db, so you don’t have to worry about bothering with a wandering request or administrative action.