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!
>>
>>
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