PostgreSQL table lock for certain select wheres

Question

Let's say I have a postgres database that tracks cars purchased by users. Every user can own as many vehicles as she wants, but only one for each make.

-----------------
| USER |
|---------------|
| id | name |
|---------------|
| 1 | Adam |
| 2 | Julia |
-----------------
-----------------
| MAKE |
|---------------|
| id | name |
|---------------|
| 1 | Volvo |
| 2 | Opel |
-----------------
--------------------------
| PURCHASE |
|----|---------|---------|
| id | user_id | make_id |
|----|---------|---------|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
--------------------------

Purchase uniqueness is restricted on a database level by a "unique" constraint (ALTER TABLE purchase ADD UNIQUE (user_id, make_id)).

When user tries to purchase a new vehicle my application first checks (in a db transaction) whether the user/make combination is possible, then inserts a new row

SELECT COUNT(1) FROM purchase WHERE user_id = 2 AND make_id = 1;
// assuming the previous query found no records
INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

The thing is, in a rare race-condition situation, when there are two concurrent requests, this opens a possibility that two separate app instances will get the information that the user/make combination does not exist and will try adding a new row to the database. The second one will obviously fail due to uniqueness restraint on the purchase table, however I was wondering if it was possible for a transaction to create a lock on a postgres table that would prevent any other transactions from making selects for a specific WHERE cause (in my situation - WHERE user_id = 2)? I cannot block the whole table as it would have a horrific impact on the app performance. Just lock selects for some (may be non-existing) rows. How do I do that?

Answer 1

I think this could be achieved with an advisory lock

Before doing the insert every transaction would acquire an advisory lock e.g. for the user_id/make_id combination, so any subsequent transaction that tries to do something for the same user/make, would have to wait:

-- this waits until the lock can be granted
select pg_advisory_lock(2,1);
SELECT COUNT(*) 
FROM purchase 
WHERE user_id = 2 
 AND make_id = 1;
INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

You can also use pg_try_advisory_lock() which returns immediately with a boolean result whether the lock could be granted or not, if you don't want the session to wait for the lock (but show an error message "already locked" or something similar).

The lock is automatically released when you commit (or rollback) your transaction (so you have to make sure to turn off autocommit and wrap all statements in a single transaction)

---

As deszo pointed out: it's important that all involved parts of the code stick the to the contract that an advisory lock is required. The approach using an advisory lock does not guarantee in any way that conflicts may occur.

• ypercubeᵀᴹ: Would it be possible to use the ON CONFLICT DO NOTHING syntax or can that lead to race conditions as well?
• András Váczi: Important to note that advisory locks only work well when all parties involved observe them. In my experience they best work when the only source of the change (the INSERT in this case) is a single function in PostgreSQL (or a single procedure of application code).
• user1822: @ypercubeTM that would be possible as well - but my understanding was that Paul actively wanted to "know" that some other transaction is going on.