Subject: Guarantee order of batched pg_advisory_xact_lock

From: Nico Heller <[email-masked]>
Date: Wed, 11 Feb 2026 18:44:07 +0000

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 11 Feb 2026 16:17:54 -0500

> We thought this would be enough, but we occasionally still run into 

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

---

From: Nico Heller <[email-masked]>
Date: Wed, 11 Feb 2026 21:51:40 +0000

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.