Обсуждение: Guarantee order of batched pg_advisory_xact_lock

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

Nico Heller <nico.heller@posteo.de> 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

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 <nico.heller@posteo.de> 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

Nico Heller <nico.heller@posteo.de> writes:
> So it would probably be better to ORDER BY the hashtextended result 
> instead of :keysToLock, right?

Yeah, that seems like it'd work, if you have no other dependencies
on the locking order.

            regards, tom lane

I just checked for hash collisions with the following query today:

SELECT COUNT(*), hashtextextended(key, 0) FROM
(
  SELECT key FROM table1
  UNION
  SELECT key FROM table2
  UNION
  ...
) keys (key)
GROUP BY hashtextextended(key, 0)
HAVING COUNT(*) > 1

Where table1, table2, ... are all the tables we are acquire keys from to use for the mentioned query.

Sadly, no results were returned. Thus, I can rule out hash collisions.

Any other thoughts? Here is an error log from the JDBC driver:

Nico Heller <nico.heller@posteo.de> writes:

So it would probably be better to ORDER BY the hashtextended result 
instead of :keysToLock, right?

Yeah, that seems like it'd work, if you have no other dependencies
on the locking order.
			regards, tom lane

For my case that is not true as our keys are globally unique URN strings. Thus, only the hashes may collide, but thank you for the insight.

On Thu, Feb 12, 2026 at 6:18 AM Nico Heller <nico.heller@posteo.de> wrote:

I just checked for hash collisions with the following query today:

SELECT COUNT(*), hashtextextended(key, 0) FROM
(
  SELECT key FROM table1
  UNION 

 

FWIW, you need UNION ALL, not UNION, if you are trying to detect duplicate values (hashed or not) across tables.

Cheers,

Greg

Does anyone have any idea what the root cause of my issue is? I appreciate any insight.
As I said, hash collisions can be rules out, sadly.

I just checked for hash collisions with the following query today:

SELECT COUNT(*), hashtextextended(key, 0) FROM
(
  SELECT key FROM table1
  UNION
  SELECT key FROM table2
  UNION
  ...
) keys (key)
GROUP BY hashtextextended(key, 0)
HAVING COUNT(*) > 1

Where table1, table2, ... are all the tables we are acquire keys from to use for the mentioned query.

Sadly, no results were returned. Thus, I can rule out hash collisions.

Any other thoughts? Here is an error log from the JDBC driver:

org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 60780 waits for ExclusiveLock on advisory lock [24605,3030106527,494580150,1]; blocked by process 65280.                                                                                                                  Process 65280 waits for ExclusiveLock on advisory lock [24605,1321834016,1311356115,1]; blocked by process 60780.

On 2/11/26 23:49, Tom Lane wrote:

Nico Heller <nico.heller@posteo.de> writes:

So it would probably be better to ORDER BY the hashtextended result 
instead of :keysToLock, right?

Yeah, that seems like it'd work, if you have no other dependencies
on the locking order.
			regards, tom lane

On Mon, Feb 16, 2026 at 12:45 PM Nico Heller <nico.heller@posteo.de> wrote:

Does anyone have any idea what the root cause of my issue is? I appreciate any insight.
As I said, hash collisions can be rules out, sadly.

Well, you could set log_statement to 'all' for a bit to see *exactly* what each of the deadlocking processes are doing. Alternatively, perhaps you can write a hashextendedkey() function that outputs arguments and results to a log and/or a table.

keysToLock is a text[] parameter which is pre-sorted in our application

Would not hurt to triple-check this part as well. Could show us the app code? Maybe put in some sort of global assert in the app to verify that things are indeed sorted as you think they are.

    @Transactional(propagation = MANDATORY) // enforces a transaction is already active, using pg_advisory_xact_lock is senseless otherwise
    fun acquireLocks(keys: List<String>) = sqlClient.query(<QUERY_HERE>).param(:keys, sort(keys)).execute()
   
    private fun sort(keys: List<String>) = keys.sort() // as I said, it's sorted in some arbitrary way }

Cheers,

Greg

--

Crunchy Data - https://www.crunchydata.com

Enterprise Postgres Software Products & Tech Support