pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-general@lists.postgresql.org
Cc: Achilleas Mantzios <itdev@gatewaynet.com>
Subject: Re: Strange deadlock with object/target of lock : transaction
Date: Wed, 21 Oct 2026 17:36:03 +0200
Message-ID: <c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com> (raw)
In-Reply-To: <aee6efad-aded-48cb-91e3-b6b502910c6e@cloud.gatewaynet.com>
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>
	<19632052-6313-44d3-a09d-d6d0e84de5c5@cloud.gatewaynet.com>
	<aee6efad-aded-48cb-91e3-b6b502910c6e@cloud.gatewaynet.com>


On 8/26/25 09:38, Achilleas Mantzios wrote:
>
> On 8/25/25 17:11, Achilleas Mantzios wrote:
>
>>
>> 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.
>
> Hi, trying to create a test case, I think I am up to something. I 
> attach the test table and data.
>
> How to reproduce. We have the "insert" code and the "update" code. The 
> "update" code runs :
>
> with tempqry as (select year,doc_no from test_deadlock where 
> delivered_at::date = current_date
>
> and not sign_list order by 
> bank_name,management_company_name,beneficiary_name,year,doc_no )
>
> UPDATE test_deadlock pf set sign_list = true FROM tempqry
>
> WHERE pf.year = tempqry.year and pf.doc_no = tempqry.doc_no ;
>
> The  insert code runs :
>
> select COALESCE(max(id),0)+1  FROM test_deadlock;
>
> INSERT INTO test_deadlock (id,year, doc_no, inserted_at, bank_name, 
> management_company_name,beneficiary_name, currency, delivered_at, 
> sign_list) VALUES (333024,2025, 395303, '2025-08-26 01:00:00+03', 
> 'CRRRRRRRRRRR', '0006-D', 'AUTAUTAUTAUTAUTAUTAUTAUTAUTAUT', 'EUR', 
> '2025-08-26 11:55:28.359485+03', false);
>
> DO $$
>
> DECLARE REC record;
>
> BEGIN
>
>       FOR REC IN SELECT year,doc_no FROM test_deadlock WHERE NOT 
> sign_list ORDER BY
>
> bank_name,management_company_name,beneficiary_name,year,doc_no LOOP
>
>          UPDATE test_deadlock SET currency=currency WHERE 
> year=REC.year AND doc_no=REC.doc_no;
>
>       END LOOP;
>
> END$$
> ;
>
> As you can notice, the order of the updates in both the "update" code 
> and the "insert" code is consistent (same order by 
> bank_name,management_company_name,beneficiary_name,year,doc_no) and 
> one would expect there would no deadlock .
>
> Now I open 3 sessions , 1, 2 and 3 :
>
> Session 1, running the "update" code :
>
> amantzio@[local]/dynacom=# begin; select txid_current(), 
> pg_backend_pid() ;  with tempqry as (select year,doc_no from 
> test_deadlock where delivered_at::date = current_date and not sign_lis
> t order by 
> bank_name,management_company_name,beneficiary_name,year,doc_no ) 
> UPDATE test_deadlock pf set sign_list = true FROM tempqry WHERE 
> pf.year = tempqry.year and pf.doc_no = tempqry.d
> oc_no ;
> BEGIN
> txid_current | pg_backend_pid
> --------------+----------------
>    117290416 |           2336
> (1 row)
>
> UPDATE 6
> amantzio@[local]/dynacom=*#
>
> Session 2, like session 1, also "update" :
>
> amantzio@[local]/dynacom=# begin; select txid_current(), 
> pg_backend_pid() ;  with tempqry as (select year,doc_no from 
> test_deadlock where delivered_at::date = current_date and not sign_lis
> t order by 
> bank_name,management_company_name,beneficiary_name,year,doc_no ) 
> UPDATE test_deadlock pf set sign_list = true FROM tempqry WHERE 
> pf.year = tempqry.year and pf.doc_no = tempqry.d
> oc_no ;
> BEGIN
> txid_current | pg_backend_pid
> --------------+----------------
>    117290418 |           2056
> (1 row)
>
> (waits for session 1's xaction to finish , as expected )
>
> Session 3, running the "insert" code :
>
> begin ; select txid_current() , pg_backend_pid()  ; select 
> COALESCE(max(id),0)+1  FROM test_deadlock; INSERT INTO test_deadlock 
> (id,year, doc_no, inserted_at, ba
> nk_name, management_company_name,beneficiary_name, currency, 
> delivered_at, sign_list) VALUES (333024,2025, 395303, '2025-08-26 
> 01:00:00+03', 'CRRRRRRRRRRR', '0006-D', 'AUTAUTAUTAUTAUTAUTAU
> TAUTAUTAUT', 'EUR', '2025-08-26 11:55:28.359485+03', false); DO 
> $$DECLARE REC record; BEGIN
> FOR REC IN SELECT year,doc_no FROM test_deadlock WHERE NOT sign_list 
> ORDER BY 
> bank_name,management_company_name,beneficiary_name,year,doc_no LOOP
> UPDATE test_deadlock SET currency=currency WHERE year=REC.year AND 
> doc_no=REC.doc_no;
> END LOOP;
> END$$
> ;
> BEGIN
> txid_current | pg_backend_pid
> --------------+----------------
>    117290419 |           2039
> (1 row)
>
> ?column?
> ----------
>   333023
> (1 row)
>
> INSERT 0 1
>
> (waits ....)
>
> Now I return to session 1 and give :
>
> amantzio@[local]/dynacom=*# rollback ;
> ROLLBACK
> amantzio@[local]/dynacom=#
>
> Immediately after , session 3 executes the anonymous block, but 
> session 2 :
>
> ERROR:  deadlock detected
> DETAIL:  Process 2056 waits for ShareLock on transaction 117290419; 
> blocked by process 2039.
> Process 2039 waits for ShareLock on transaction 117290418; blocked by 
> process 2056.
> HINT:  See server log for query details.
> CONTEXT:  while updating tuple (8,33) in relation "test_deadlock"
> amantzio@[local]/dynacom=!#
>
> If I update the "insert" code to use a CTE instead of the anonymous 
> block in session 3, while Session 1 and 2 commands ("update" code) 
> remain unchanged  ,
>
> session 1 (re-run as above)
>
> session 2 (re-run as above)
>
> session 3 :
>
> postgres@[local]/dynacom=# begin ; select txid_current() , 
> pg_backend_pid()  ; select COALESCE(max(id),0)+1  FROM test_deadlock; 
> INSERT INTO test_deadlock (id,year, doc_no, inserted_at, ba
> nk_name, management_company_name,beneficiary_name, currency, 
> delivered_at, sign_list) VALUES (333024,2025, 395303, '2025-08-26 
> 01:00:00+03', 'CREDIT SUISSE AG', '0006-DYNACOM TANKERS MANAG
> EMENT LTD', 'AUTUMN SHIPPING SERVICES LIMITED', 'EUR', '2025-08-26 
> 11:55:28.359485+03', false); with tempqrybig as (select year,doc_no 
> from test_deadlock where not sign_list order by bank_
> name,management_company_name,beneficiary_name,year,doc_no) UPDATE 
> test_deadlock pf set currency=currency FROM tempqrybig WHERE pf.year = 
> tempqrybig.year and pf.doc_no = tempqrybig.doc_no ;
> BEGIN
> txid_current | pg_backend_pid
> --------------+----------------
>    117290454 |           2039
> (1 row)
>
> ?column?
> ----------
>   333023
> (1 row)
>
> INSERT 0 1
>
> (waits)
>
> then I go to Session 1, give rollback, session 2 completes, then I 
> rollback session 2, session 3 completes and finally I rollback session 
> 3, without any deadlock.
>
> Another finding , if I keep the anonymous block , but add a primary 
> key on (year, doc_no)
>
> alter table test_deadlock ADD CONSTRAINT test_deadlock_pk PRIMARY KEY 
> (year, doc_no);
>
> and repeat the initial version of the 3 sessions, again the deadlock 
> goes away.
>
> All the above with 18beta3 .
>
> So, I am not so sure this should happen, since there is no obvious 
> inconsistent order of updates. You may reproduce or ask me to run 
> again the tests while observing pg_locks or anything else.
>
> Thank you.
>

Hi ppl, I am writing from Valencia, attending 2026.pgconf.eu , after I 
watched a talk by Laurenz Albe, and talking to him (thanks Laurenz for 
the talk and chat), it made me go back look into this thread, research 
it a little bit, and realize that there was no definite answer as to 
what happened. I am referring to our real problem at the beginning and 
not the various attempts to make a reproducible case.

So, what happened, the INSERT by it self acquires a |ROW EXCLUSIVE| 
(|RowExclusiveLock|) on the table even before the BEFORE trigger is 
called.  A ROW EXCLUSIVE lock does not block another session acquiring 
also a  LOW EXCLUSIVE lock on same table.

But what the BEFORE trigger did was

LOCK TABLE payments_finalization IN SHARE UPDATE EXCLUSIVE MODE;

which is stronger than ROW EXCLUSIVE and conflicts with itself on the 
same table.

So what was happening is that :

session 1                                              session  2

issue INSERT stmt                               issue INSERT stmt

pgsql acquires a ROW EXCLUSIVE on the table for both sessions, ROW 
EXCLUSIVE is not self-conflicting.

Then both backends calle the BEFORE trigger.

Both get to execute the explicit LOCK TABLE payments_finalization IN 
SHARE UPDATE EXCLUSIVE MODE ;

the first session waits on the second to release its ROW EXCLUSIVE lock.

the second session waits on the first to release its ROW EXCLUSIVE lock.

A clear dead-lock situation.

The trick was to realize that the normal , expected and documented : ROW 
EXCLUSIVE on INSERT is called BEFORE the BEFORE trigger !

I don't know where and if this is documented, anyway, I felt I had to 
complete the history of this problem.


For the EU folks in Valencia, see you around, have a great time!


>>
>>

view thread (12+ messages)  latest in thread

Message-ID: <c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com>
Permalink:  ../c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com/
Also on:    postgresql.org/message-id/c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: a.mantzios@cloud.gatewaynet.com, itdev@gatewaynet.com
  Subject: Re: Strange deadlock with object/target of lock : transaction
  In-Reply-To: <c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com>
  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> <19632052-6313-44d3-a09d-d6d0e84de5c5@cloud.gatewaynet.com> <aee6efad-aded-48cb-91e3-b6b502910c6e@cloud.gatewaynet.com> <c7c8856d-23dc-43bf-aa50-9d97f0b65157@cloud.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox