From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nico Heller <nico.heller@posteo.de>
cc: pgsql-general@postgresql.org
Subject: Re: Guarantee order of batched pg_advisory_xact_lock
In-reply-to: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de>
References: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de>
Comments: In-reply-to Nico Heller <nico.heller@posteo.de>
	message dated "Wed, 11 Feb 2026 18:44:07 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <14505.1770844674.1@sss.pgh.pa.us>
Date: Wed, 11 Feb 2026 16:17:54 -0500
Message-ID: <14506.1770844674@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/14506.1770844674%40sss.pgh.pa.us>
Precedence: bulk

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