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

Reply via email to