Clarification of deadlock possibilities in section 13.3.5. Advisory Locks

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: marius(dot)lichtblau(at)googlemail(dot)com
Subject: Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
Date: 2023-09-05 09:36:45
Message-ID: 169390660571.627.9771437300071326105@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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

Browse pgsql-docs by date

  From Date Subject
Next Message Bruce Momjian 2023-09-06 01:42:16 Replica vs standby
Previous Message Erik Wienhold 2023-09-01 15:16:52 Re: Error in Example