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

• user1822: Hmm, why not just use SELECT ... FOR UPDATE?
• cis: That didn't work for me either in my first test. And I don't know how precisely it is supposed to work. How does Postgres know "who" did obtain the lock - by session id? What if in between, a network error happens and the clients needs to reconnect - will this LOCK stay forever?
• user1822: The transaction that ran the 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)
• cis: No, nothing is done in a transaction. Between SELECT and UPDATE I am (and I have to) on application level.
• jjanes: "As you see, my statements have parameters. That's why I cannot use transactions." This makes no sense. How do you think parameters preclude transactions?

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

• cis: I see. But what if between the UPDATE and pg_advisory_unlock something unexpected happens in the sense of a connection loss or an application crash? I guess, in that case, the lock might remain forever. As mentioned, using a transaction would be inconvenient for me due to params.
• Daniel Vérité: @cis: the maximum lifetime of the lock is the session. When the session goes away, the lock goes away.