On 1/28/22 20:54, Michael Lewis 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
