PostgreSQL – How to Lock Tables for Specific Select Queries

lockingpostgresql

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?

Best Answer

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.

Related Question