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?