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
