public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nico Heller <[email protected]>
To: [email protected]
Subject: Guarantee order of batched pg_advisory_xact_lock
Date: Wed, 11 Feb 2026 18:44:07 +0000
Message-ID: <[email protected]> (raw)

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

view thread (3+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: Guarantee order of batched pg_advisory_xact_lock
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox