From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: marius.lichtblau@googlemail.com
Subject: Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
Date: Tue, 05 Sep 2023 09:36:45 +0000
Message-ID: <169390660571.627.9771437300071326105@wrigleys.postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, marius.lichtblau@googlemail.com
Subject: Re: Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
In-Reply-To: <169390660571.627.9771437300071326105@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox