public inbox for [email protected]
help / color / mirror / Atom feedGuarantee order of batched pg_advisory_xact_lock
3+ messages / 2 participants
[nested] [flat]
* Guarantee order of batched pg_advisory_xact_lock
@ 2026-02-11 18:44 Nico Heller <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Nico Heller @ 2026-02-11 18:44 UTC (permalink / raw)
To: pgsql-general
Good day,
I am working on a system which re-centralizes a distributed system to
publish the aggregated data somewhere.
We make heavy use of advisory locks to prevent race conditions in our
application.
We use the following bulk query as we sometimes need acquire multiple
locks at the same time and want to avoid round-trips to the database:
|WITH keys(key) AS (SELECT unnest(:keysToLock)) SELECT
pg_advisory_xact_lock(hashtextextended(key, 0)) FROM keys|
:keysToLock is a text[] parameter which is pre-sorted in our
application. This pre-sorting is done to prevent dead locks when two
concurrent transactions try acquire the same advisory locks (e.g.
[a,b,c] [b,a,c] can easily deadlock).
We thought this would be enough, but we occasionally still run into
deadlocks.
I tried to research this topic and learned that the SQL standard does
not guarantee the order of execution without ORDER BY, so I whipped up
the following variant:
|SELECT pg_advisory_xact_lock(hashtextextended(ordered_keys.key, 0))
FROM ( SELECT * FROM unnest(?) WITH ORDINALITY keys(key, index)
ORDER BY index ) ordered_keys|
Would this suffice? It's really difficult for me to find reliable
documentation about this topic.
A user on StackOverflow suggested this variant to create an
"optimization fence" so that the subquery cannot be flattened:
|SELECT pg_advisory_xact_lock(hashtextextended(ordered_keys.key, 0))
FROM (SELECT * FROM unnest(?) WITH ORDINALITY AS keys(key, index)
ORDER BY index /* a no-op, but it prevents subquery flattening */
OFFSET 0) AS ordered_keys;|
Somehow, wanting a guaranteed order of pg_advisory_xact_lock execution
turned out to be quite complicated.
So what is the correct way to do this? And I would love for some form of
documentation link to read up on this.
Thank you for your time,
Nico Heller
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
@ 2026-02-11 21:17 Tom Lane <[email protected]>
parent: Nico Heller <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2026-02-11 21:17 UTC (permalink / raw)
To: Nico Heller <[email protected]>; +Cc: pgsql-general
Nico Heller <[email protected]> writes:
> We use the following bulk query as we sometimes need acquire multiple
> locks at the same time and want to avoid round-trips to the database:
> |WITH keys(key) AS (SELECT unnest(:keysToLock)) SELECT
> pg_advisory_xact_lock(hashtextextended(key, 0)) FROM keys|
> :keysToLock is a text[] parameter which is pre-sorted in our
> application. This pre-sorting is done to prevent dead locks when two
> concurrent transactions try acquire the same advisory locks (e.g.
> [a,b,c] [b,a,c] can easily deadlock).
> We thought this would be enough, but we occasionally still run into
> deadlocks.
Have you eliminated the possibility that you're getting hash
collisions? With or without that CTE, I can't see a reason for
PG to change the order in which the unnest() results are processed,
so I think you are barking up the wrong tree about where the
problem is.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
@ 2026-02-11 21:51 Nico Heller <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Nico Heller @ 2026-02-11 21:51 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-general
That's an interesting idea and more likely, yes - I didn't think of that.
So it would probably be better to ORDER BY the hashtextended result
instead of :keysToLock, right?
Hash collisions could therefore not create the [a,b,c] [b,a,c] locking
pattern which obviously deadlocks.
I will check for hash collisions tomorrow, I know all possible keys.
On 2/11/26 22:17, Tom Lane wrote:
> Nico Heller <[email protected]> writes:
>> We use the following bulk query as we sometimes need acquire multiple
>> locks at the same time and want to avoid round-trips to the database:
>> |WITH keys(key) AS (SELECT unnest(:keysToLock)) SELECT
>> pg_advisory_xact_lock(hashtextextended(key, 0)) FROM keys|
>> :keysToLock is a text[] parameter which is pre-sorted in our
>> application. This pre-sorting is done to prevent dead locks when two
>> concurrent transactions try acquire the same advisory locks (e.g.
>> [a,b,c] [b,a,c] can easily deadlock).
>> We thought this would be enough, but we occasionally still run into
>> deadlocks.
> Have you eliminated the possibility that you're getting hash
> collisions? With or without that CTE, I can't see a reason for
> PG to change the order in which the unnest() results are processed,
> so I think you are barking up the wrong tree about where the
> problem is.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-11 21:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-11 18:44 Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
2026-02-11 21:17 ` Tom Lane <[email protected]>
2026-02-11 21:51 ` Nico Heller <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox