Thread: Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
From
PG Doc comments form
Date:
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