Message-ID: <19632052-6313-44d3-a09d-d6d0e84de5c5@cloud.gatewaynet.com>
Date: Mon, 25 Aug 2025 19:11:26 +0300
MIME-Version: 1.0
Subject: Re: Strange deadlock with object/target of lock : transaction
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 pgsql-general@lists.postgresql.org
References: <0c474bc1-e7d6-4d7f-88ad-5284f89c997b@cloud.gatewaynet.com>
 <d39c5f45-1c4c-4225-8462-20a1cf10c173@aklaver.com>
 <48a32f45-57f2-4560-ae94-3488b3568c8a@cloud.gatewaynet.com>
 <1fff675a-61a7-4ad7-8871-7ae7f5bb7ac7@cloud.gatewaynet.com>
 <01f1e49c-50ac-4aa8-9b82-b73fddbda8ef@cloud.gatewaynet.com>
 <34cc0d5c-4128-4664-9166-acbba9cdf57f@aklaver.com>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <34cc0d5c-4128-4664-9166-acbba9cdf57f@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/19632052-6313-44d3-a09d-d6d0e84de5c5%40cloud.gatewaynet.com>
Precedence: bulk


On 25/8/25 17:58, Adrian Klaver wrote:
> On 8/25/25 07:40, Achilleas Mantzios wrote:
>> On 8/20/25 14:59, Achilleas Mantzios wrote:
>>
>>> On 8/14/25 16:01, Achilleas Mantzios wrote:
>>>
>>>> Hi Adrian
>>>>
>>>> On 8/14/25 15:39, Adrian Klaver wrote:
>>>>
>>>>> On 8/14/25 00:07, Achilleas Mantzios wrote:
>>>>>> Hi All
>>>>>>
>>>>>> We've been hit by a weird deadlock which it took me some days to 
>>>>>> isolate and replicate. It does not have to do with order of 
>>>>>> updates or any explicit TABLE-level locking, the objects/targets 
>>>>>> of the deadlock in question are transactions.
>>>>>
>>>> First off, I maybe wrong with the above conclusion, I noticed that 
>
>> Hi I reproduced without the triggers, I understood the problem, I 
>> believe the system's behavior is the intended, I am sorry for the 
>> false alarm. The thing is that it takes >=3 transactions to happen . 
>> That was the tricky part, up to now in all cases of deadlocks we had 
>> two transactions involved, this one needed three or more.
>
> For folks that run across this thread what was the issue?
Inconsistent order of updates. The two pieces of code , the update piece 
and the insert piece, used inconsistent order of updates. However this 
could not be manifested with one xaction of the update-type and one of 
the insert-type, there had to be more than one transactions of the 
update-type doing the same update (usually caused by users hitting the 
reload button after 1 or 2 seconds). I can easily prepare a test case, 
schema, data, commands for anyone interested.