Thread: pg_try_advisory_lock is waiting?

I am trying to replace SELECT <colum list> FROM <table> WHERE <condition> FOR UPDATE with pg_try_advisory_lock. The documentation says the following:

https://www.postgresql.org/docs/13/functions-admin.html

pg_try_advisory_lock ( key bigint ) → boolean

pg_try_advisory_lock ( key1 integer, key2 integer ) → boolean

Obtains an exclusive session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.

I tried the following:

1st Session:

mgogala=# begin transaction;
BEGIN
mgogala=*# update emp set sal=sal*1 where empno=7934;
UPDATE 1
mgogala=*#

2nd Session:

mgogala=# begin transaction;
BEGIN
mgogala=*# select pg_try_advisory_lock(0) from (select ename from emp where empno=7934 for update) as tbl;

To my infinite surprise, "pg_advisory_lock" is waiting. I am aware of SELECT FOR UPDATE NOWAIT, but that produces an error and kills the transaction block. I would like to use something that would not kill the transaction block. I am obviously doing something wrong because the select in parenthesis will not return, so the query cannot be executed.  On the other hand, without the "FOR UPDATE" clause, I am getting TRUE, which is wrong:

mgogala=# begin transaction;
BEGIN
mgogala=*# select pg_try_advisory_xact_lock(0) from (select ename from emp where empno=7934) as tbl;
 pg_try_advisory_xact_lock
---------------------------
 t
(1 row)

mgogala=*# rollback;
ROLLBACK
mgogala=# select pg_try_advisory_xact_lock(1) from (select ename from emp where empno=7934) as tbl;
 pg_try_advisory_xact_lock
---------------------------
 t
(1 row)

The row is still locked by the UPDATE statement, so the try_advisory_lock should return "f", not "t". The database is 13.5 on  Oracle Linux 8, x86_64. Transactions are written in Java so an exception will terminate the transaction block. SQL statements are generated by the home grown ORM. The application is ported from Oracle which will not hang the transaction block on the 1st error. Is there a way to get PostgreSQL to use something like NOWAIT without aborting the transaction block?

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Mladen Gogala <gogala.mladen@gmail.com> writes:
> I tried the following:

> _*1st Session:*_

> mgogala=# begin transaction;
> BEGIN
> mgogala=*# update emp set sal=sal*1 where empno=7934;
> UPDATE 1
> mgogala=*#

> _*2nd Session:*_

> mgogala=# begin transaction;
> BEGIN
> mgogala=*# select pg_try_advisory_lock(0) from (select ename from emp 
> where empno=7934 for update) as tbl;

> To my infinite surprise, "pg_advisory_lock" is waiting.

I doubt it.  I think the FOR UPDATE in the sub-select is blocked
because the other session has an uncommitted update on the row
it wants to lock.  This command won't reach the pg_try_advisory_lock
call until that row lock comes free.

            regards, tom lane

I doubt it.  I think the FOR UPDATE in the sub-select is blocked
because the other session has an uncommitted update on the row
it wants to lock.  This command won't reach the pg_try_advisory_lock
call until that row lock comes free.

Yes, I figured it out, but 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?

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala <gogala.mladen@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

On Fri, Jan 28, 2022 at 6:34 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:
>
> On 1/28/22 19:08, Tom Lane wrote:
>
> I doubt it.  I think the FOR UPDATE in the sub-select is blocked
> because the other session has an uncommitted update on the row
> it wants to lock.  This command won't reach the pg_try_advisory_lock
> call until that row lock comes free.
>
> Yes, I figured it out, but pg_try_advisory_lock returned TRUE even without "FOR UPDATE" clause in the subquery.
Shouldn'tit return false because it can't lock the row until the uncommitted update finishes?
 

advisory locks and row locks are completely distinct and separate.
It's also not a good idea to make any assumptions on order of
operations as to which lock is acquired first using subqueries in that
fashion.

merlin