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
pg_try_advisory_lock and other advisory lock functions take a lock on a
key. The keys you have tried to lock are 0 and 1. Why?
What problem are you trying to solve by changing from "for update" lock to
advisory lock anyway?
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
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'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?<https://dbwhisperer.wordpress.com>
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.
On 1/28/22 20:54, Michael Lewis wrote:
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't it 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