Guarantee order of batched pg_advisory_xact_lock

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.

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:

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 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

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.

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.

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.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

On 2/17/26 15:55, Greg Sabino Mullane wrote:

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.

I will give that a shot, thank you

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.

I am 100% sure this is the case, as the code base isn't huge and we have
central component which is *always* used to acquire advisory locks.
It looks as follows, in pseudo Kotlin code:

class LockingRepository(val sqlClient: SqlClient) {

    @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

}