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
