pg_try_advisory_lock is waiting?

From: Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_try_advisory_lock is waiting?
Date: 2022-01-28 23:05:18
Message-ID: 54e6a2bc-3546-88c7-0382-c74f23e4b668@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Lewis 2022-01-28 23:14:40 Re: pg_try_advisory_lock is waiting?
Previous Message Andreas Joseph Krogh 2022-01-28 15:05:51 Will Barman support restore of single database?