I'm currently using Postgres 14 and have a problem related to concurrency control.
Let's say we have 2 transactions. Transaction A and transaction B. Transaction A acquires exclusive locks on multiple tables and transaction B has to wait until the transaction A releases the locks to be able to run insert queries into the locked tables. The transaction A finishes and transaction B successfully runs the insert queries.
The problem is that I only want to block other transactions from inserting conditionally.
To simplify the problem, let's say we have only 1 table in question and two of the previously mentioned transactions
| id | manufacturer |
|---|---|
| 1 | Volkswagen |
| 2 | Ferrari |
If the Transaction A is running, I want to prevent all inserts for Transaction B (and all possible concurrent transactions) that contain Volkswagen value for manufacturer column and allow all other inserts.
I was considering a solution with a trigger before insert. The idea was to use some kind of a lock that would relate to any kind of an identifier for the column in question and the value for that column based on which I would conditionally block all transactions that run insert queries until the running transaction has released the lock.
I was reading a bit about advisory locks but I don't have a good understanding of them yet.
CodePudding user response：
You would need a table which lists all manufacturers (just once each) and then have a rule that before anyone can insert into your shown table, they need to lock the corresponding row of the manufacturer table, like with SELECT 1 from manufacturers WHERE manufacturer='Volkswagen' FOR UPDATE