Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uqnl5-00DNCw-7n for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 07:06:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uqnl4-002BHo-LW for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 07:06:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uqnl4-002BHf-Aq for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 07:06:18 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uqnl2-001uNJ-1T for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 07:06:18 +0000 Content-Type: multipart/alternative; boundary="------------msMfwsj0WG0Sm0RFXP8i0k34" Message-ID: <5f4780f9-4b4c-4fe8-903e-a46655cf0a7b@cloud.gatewaynet.com> Date: Tue, 26 Aug 2025 08:06:13 +0100 MIME-Version: 1.0 Subject: Re: Strange deadlock with object/target of lock : transaction To: Laurenz Albe , pgsql-general@lists.postgresql.org References: <0c474bc1-e7d6-4d7f-88ad-5284f89c997b@cloud.gatewaynet.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> <0b74ce6492f901730776a620ff411b8369dc443d.camel@cybertec.at> Content-Language: en-US From: Achilleas Mantzios In-Reply-To: <0b74ce6492f901730776a620ff411b8369dc443d.camel@cybertec.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------msMfwsj0WG0Sm0RFXP8i0k34 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 8/26/25 07:22, Laurenz Albe wrote: > On Mon, 2025-08-25 at 15:40 +0100, Achilleas Mantzios wrote: >>>>>> 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. >> 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. > Yes, waiting for a transaction means that you are waiting for a row lock. > Seehttps://www.cybertec-postgresql.com/en/row-locks-in-postgresql/ Thanks Laurenz, will definitely read it. > Yours, > Laurenz Albe --------------msMfwsj0WG0Sm0RFXP8i0k34 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

On 8/26/25 07:22, Laurenz Albe wrote:

On Mon, 2025-08-25 at 15:40 +0100, Achilleas Mantzios wrote:

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.

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.

Yes, waiting for a transaction means that you are waiting for a row lock.
See https://www.cybertec-postgresql.com/en/row-locks-in-postgresql/

Thanks Laurenz, will definitely read it.

Yours,
Laurenz Albe

--------------msMfwsj0WG0Sm0RFXP8i0k34--