Re: Strange deadlock with object/target of lock : transaction

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(at)[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(at)[local]/dynacom=*#
>
> Session 2, like session 1, also "update" :
>
> amantzio(at)[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(at)[local]/dynacom=*# rollback ;
> ROLLBACK
> amantzio(at)[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(at)[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(at)[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!

>>
>>