Using Postgres 13 in a Node.js application, I have a simple task to do:
SELECT
a row (identified by its primary key)- Run some application code that cannot be done in the database* with that data.
UPDATE
the row.
Edit: Because of the stuff happening on application level and outside of the database, I cannot use a transaction.
The problem is: concurrency. I need to make sure that the data fetched in SELECT
is still the same when the UPDATE
happens for that very row. If another process changes the data in between, then the UPDATE
gets messy. So, I need some kind of locking so that other processes need to wait/retry.
What kind of locking would I need here?
I think it's the advisory locking.
If so: How can I integrate those into my SELECT
and UPDATE
query?
My idea was something like
SELECT my_id, my_column FROM my_table WHERE my_id = $1 AND pg_try_advisory_lock(my_id);
(if that doesn't return anything, I do not proceed)
and
UPDATE my_table SET my_column = $2
WHERE my_id = $1 AND pg_advisory_unlock(my_id) IS TRUE
RETURNING my_column;
However, that doesn't work. Concurrency seems still to happen. I don't know why. For reasons unknown to me, the UPDATE
doesn't return anything sometimes if the condition AND pg_advisory_unlock(my_id) IS TRUE
is present.
What am I missing here?
I found a ton of articles explaining the idea behind advisory locks but nothing on how to integrate them into your code. As you see, my statements have parameters. That's why I cannot use transactions. Using functions would also be problematic for some non-DB-related reasons. (Version control system doesn't like them.)
(*I know you guys like to challenge statements like "cannot be done in the database". But in my case, it's not only technical but also organizational reasons why this code needs to be executed on application level and cannot be done in the DB. So, for the sake of the question, please just accept it as a matter of fact.)
SELECT ... FOR UPDATE
?LOCK
stay forever?select for update
will hold the lock. And the lock will go away once your transaction commits (this assumes that everything is done in a single transaction)SELECT
andUPDATE
I am (and I have to) on application level.