The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/explicit-locking.html
Description:
Hi,
An example from section 13.3.5. Advisory Locks (I included the ORDER BY for
deterministic results):
SELECT pg_advisory_lock(q.id) FROM
(
SELECT id FROM foo WHERE id > 12345 ORDER BY id ASC LIMIT 100
) q; -- ok
Can you clarify the potential for deadlocks occurring when two transactions
execute this at the same time.
Assuming no concurrent inserts or updates the inner SELECT should return the
same set of ids.
However, my question is, whether the ORDER BY guarantees the order in which
the advisory locks are acquired?
I would want the guarantee that the locks are acquired in ascending order
(or at least the same order) instead of random, which would prevent the two
transactions from deadlocking.
In case the ordering is not deterministic and deadlocks are possible I think
this should be added to the documentation.
Regards,
Marius Lichtblau