Message-ID: <b461c689-1d2a-4e20-be33-e75a439d5823@posteo.de>
Date: Wed, 11 Feb 2026 21:51:40 +0000
MIME-Version: 1.0
Subject: Re: Guarantee order of batched pg_advisory_xact_lock
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
References: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de>
 <14506.1770844674@sss.pgh.pa.us>
Content-Language: en-US
From: Nico Heller <nico.heller@posteo.de>
In-Reply-To: <14506.1770844674@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b461c689-1d2a-4e20-be33-e75a439d5823%40posteo.de>
Precedence: bulk

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