Content-Type: multipart/alternative;
 boundary="------------3qaiIn0fkcwb6iTVWjS8hCFK"
Message-ID: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de>
Date: Wed, 11 Feb 2026 18:44:07 +0000
MIME-Version: 1.0
Content-Language: en-US
To: pgsql-general@postgresql.org
From: Nico Heller <nico.heller@posteo.de>
Subject: Guarantee order of batched pg_advisory_xact_lock
Archived-At: <https://www.postgresql.org/message-id/93fb937f-9f4d-4bd7-b7e6-51d5f98859d9%40posteo.de>
Precedence: bulk

This is a multi-part message in MIME format.
--------------3qaiIn0fkcwb6iTVWjS8hCFK
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------3qaiIn0fkcwb6iTVWjS8hCFK
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Good day,<br>
      <br>
      I am working on a system which re-centralizes a distributed system
      to publish the aggregated data somewhere.<br>
      We make heavy use of advisory locks to prevent race conditions in
      our application.<br>
      <br>
      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:<br>
    </p>
    <blockquote>
      <pre class="lang-sql s-code-block"><code data-highlighted="yes"
      class="hljs language-sql"><span class="hljs-keyword">WITH</span> keys(key) <span
      class="hljs-keyword">AS</span> (<span class="hljs-keyword">SELECT</span> <span
      class="hljs-built_in">unnest</span>(:keysToLock))
<span class="hljs-keyword">SELECT</span> pg_advisory_xact_lock(hashtextextended(key, <span
      class="hljs-number">0</span>)) <span class="hljs-keyword">FROM</span> keys</code></pre>
    </blockquote>
    <p>: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).<br>
      We thought this would be enough, but we occasionally still run
      into deadlocks.<br>
      <br>
      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:<br>
    </p>
    <blockquote>
      <pre class="lang-sql s-code-block"><code data-highlighted="yes"
      class="hljs language-sql"><span class="hljs-keyword">SELECT</span> pg_advisory_xact_lock(hashtextextended(ordered_keys.key, <span
      class="hljs-number">0</span>))
<span class="hljs-keyword">FROM</span> (
    <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span
      class="hljs-keyword">FROM</span> <span class="hljs-built_in">unnest</span>(?) <span
      class="hljs-keyword">WITH</span> ORDINALITY keys(key, index) <span
      class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> index
) ordered_keys</code></pre>
    </blockquote>
    <p>Would this suffice? It's really difficult for me to find reliable
      documentation about this topic.<br>
      A user on StackOverflow suggested this variant to create an
      "optimization fence" so that the subquery cannot be flattened:</p>
    <blockquote>
      <pre class="lang-sql s-code-block"><code data-highlighted="yes"
      class="hljs language-sql"><span class="hljs-keyword">SELECT</span> pg_advisory_xact_lock(hashtextextended(ordered_keys.key, <span
      class="hljs-number">0</span>))
<span class="hljs-keyword">FROM</span> (<span class="hljs-keyword">SELECT</span> <span
      class="hljs-operator">*</span>
      <span class="hljs-keyword">FROM</span> <span class="hljs-built_in">unnest</span>(?) <span
      class="hljs-keyword">WITH</span> ORDINALITY <span
      class="hljs-keyword">AS</span> keys(key, index)
      <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> index
      <span class="hljs-comment">/* a no-op, but it prevents subquery flattening */</span>
      <span class="hljs-keyword">OFFSET</span> <span class="hljs-number">0</span>) <span
      class="hljs-keyword">AS</span> ordered_keys;</code></pre>
    </blockquote>
    <p>Somehow, wanting a guaranteed order of pg_advisory_xact_lock
      execution turned out to be quite complicated.<br>
      <br>
      So what is the correct way to do this? And I would love for some
      form of documentation link to read up on this.<br>
      <br>
      Thank you for your time,<br>
      Nico Heller</p>
  </body>
</html>

--------------3qaiIn0fkcwb6iTVWjS8hCFK--