On 1/28/22 20:54, Michael Lewis wrote:
On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala
<gogala.mla...@gmail.com> wrote:
pg_try_advisory_lock returned TRUE even without "FOR UPDATE"
clause in the subquery. Shouldn't it return false because it can't
lock the row until the uncommitted update finishes?
The rows being updated or not is unrelated to advisory locks. Your
example shows you locking on key 0 and then on key 1. That should be
allowed. If you tried key 0 twice, then that is when you would get
back "false" from the function call.
You could establish a pattern of using the table OID as the first lock
key and the primary key value (if a single column) as the second
advisory lock key with the two parameter version of the function. But
it is totally up to your code to honor that advisory lock, or not.
Again, why use advisory locks and not select for update? Perhaps just
because you don't want to deal with the failed transaction? What
should happen when some other process cannot get a lock on that row?
Do you want to wait and retry? Have you looked into the "skip locked"
option? If you use "returning id" with that, you'd be able to check if
you got the lock or not.
I managed to resolve the problem:
savepoint pt1;
select from table where <...> for update nowait;
If it fails, do "rollback to pt1" and continue. Transaction block will
survive. OK, now the life is good again.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
