How to integrate Postgres advisory lock functions into SELECT and UPDATE?

Question

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.)

Answer 1

UPDATE my_table SET my_column = $2
WHERE my_id = $1 AND pg_advisory_unlock(my_id) IS TRUE 
RETURNING my_column;

There's a race condition here. The lock is released when the UPDATE hasn't happened yet.

The lock should be released in a separate statement after the UPDATE.

Besides, in the Read Committed isolation level, which is the default mode, WHERE conditions can be evaluated several times (*) if the command has to wait for a row lock (independently of your advisory lock). Even if it's not clear that a row lock can exist in your particular case, it seems like a hazard that you should eliminate. Obviously having pg_advisory_unlock(my_id) being called a second time in the same statement wreaks havoc on the locking logic here.

(*) From the documentation on Read Committed:

The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition