CODEWITHSUNDEEP
postgresqllocking
andrew
andrew

Reputation: 1243

Can I obtain a postgresql lock that prevents concurrent inserts for a portion of a table?

I have a join table attendees of rooms and users with an extra column that represents a randomly assigned string (from within a pool of strings).

I want to prevent two users entering at the same time from accidentally getting assigned the same string -- so I'd want User B to wait to look up the previous users in the room (and their assigned strings) until User A has completed inserting -- but I don't want to do a table lock since a row insertion for say room_id = 12 doesn't affect an insertion for room_id = 77.

I can't use a unique constraint to solve this, since duplicate strings are possible in the case of a large number of users in a single room (the strings get reassigned evenly once all of them have been used once).*

My guess is doing something like

SELECT room_id, user_id, random_string WHERE room_id = ? FOR UPDATE

isn't going to help because it's not going to prevent User B from doing an insert -- and even if that SELECT FOR UPDATE prevented user B from doing the same call to read the rows corresponding to that room, what happens if both User A and User B are the first ones to join (and there aren't any rows for that room_id to lock)?

Would I use an advisory lock that's keyed on the room_id? Would that still help if I had multiple concurrent writes (e.g. User A should finish first, then User B, then User C)?

*Here's the example of why the strings aren't necessarily unique: say the pool of strings is "red", "blue", "green" -- the first three users that enter are each assigned one of them randomly, then the pool resets. The next three users are also assigned them randomly, so for the six users in the room, exactly two would have "red", two "blue", and two "green".

Upvotes: 2

Views: 1505

Answers (1)

andrew
andrew

Reputation: 1243

After a frustrating few days on SO I ended up getting a helpful response on Reddit that solves the problem:

Use a SELECT FOR UPDATE query on the rooms table, locking the row that corresponds to the room until the first user’s transaction completes.

Upvotes: 3

Related Questions