Re: pg_try_advisory_lock is waiting?

On 1/28/22 20:54, Michael Lewis wrote:
> On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala
> <gogala(dot)mladen(at)gmail(dot)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