Horton

Thread

  1. Strange deadlock with object/target of lock : transaction

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-14T07:07:36Z

    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.

I show the schema of the table and its triggers functions :

amantzio@[local]/dynacom=# \d bdynacom.payments_finalization
                                                 Table 
"bdynacom.payments_finalization"
         Column          |           Type           | Collation | 
Nullable |                          Default
-------------------------+--------------------------+-----------+----------+------------------------------------------------------------ 

id                      | integer                  |           | not 
null | nextval('bdynacom.payments_finalization_id_seq'::regclass)
year                    | integer                  |           | not null |
doc_no                  | integer                  |           | not null |
accnt_ukey              | integer                  |           | not null |
inserted_at             | timestamp with time zone |           | not 
null | now()
bank_name               | text                     |           | not null |
management_company_name | text                     |           | not null |
beneficiary_name        | text                     |           |          |
currency                | text                     |           | not null |
amount                  | double precision         |           | not null |
explanation             | text                     |           | not null |
card_code               | character varying(20)    |           |          |
vsl_code                | character varying(20)    |           |          |
signed_by               | text                     |           | not null |
delivered_at            | timestamp with time zone |           | not null |
group_explanation       | text                     |           |          |
ingroup                 | boolean                  |           | 
          | false
is_transfer             | boolean                  |           | not 
null | false
bank_bic                | character varying(11)    |           | not 
null | 'XXXXXXXX'::character varying
bank_account            | character varying(35)    |           | not 
null | ''::character varying
amount_local            | double precision         |           | not 
null | 0
creditor_bank_name      | text                     |           |          |
creditor_bank_bic       | character varying(11)    |           |          |
creditor_bank_account   | character varying(35)    |           |          |
sign_list               | boolean                  |           | 
          | false
Indexes:
    "payments_finalization_pkey" PRIMARY KEY, btree (id) DEFERRABLE
    "payments_finalization_accnt_ukey_uk" UNIQUE CONSTRAINT, btree 
(accnt_ukey) REPLICA IDENTITY
    "payments_finalization_bank_account" btree (bank_account)
    "payments_finalization_delivered_at_date" btree 
(extract_date(delivered_at))
    "payments_finalization_idx1" btree (inserted_at, vsl_code, 
card_code, ingroup)
    "payments_finalization_sign_list" btree (sign_list)
    "payments_finalization_uk" UNIQUE CONSTRAINT, btree (doc_no, year)
Check constraints:
    "payments_finalization_check_ingroup_group_explanation" CHECK 
(COALESCE(ingroup, false) AND group_explanation IS NOT NULL OR NOT 
COALESCE(ingroup, false) AND group_explanation IS NUL
L)
    "valid_signatures" CHECK (signed_by = ANY (ARRAY['GP'::text, 
'EP'::text, 'IP'::text, 'MP'::text, 'NC'::text, 'MEP'::text, 'N/A'::text]))
Triggers:
    payments_finalization AFTER INSERT ON bdynacom.payments_finalization 
FOR EACH ROW EXECUTE FUNCTION payments_finalization_force_integrity()
    payments_finalization_set_epayment_finalized_tg AFTER INSERT OR 
DELETE ON bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION 
payments_finalization_set_epayment_finalized()
    payments_finalization_set_id_tg BEFORE INSERT ON 
bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION 
payments_finalization_set_id()
Inherits: payments_finalization

amantzio@[local]/dynacom=# \sf payments_finalization_set_id
CREATE OR REPLACE FUNCTION public.payments_finalization_set_id()
RETURNS trigger
LANGUAGE plpgsql
AS $function$DECLARE
        nuid INTEGER;
        footmp text;
BEGIN

        IF (TG_OP <> 'INSERT') THEN
                RAISE EXCEPTION 'TRIGGER : % called on unsuported op : 
%. ONLY INSERT IS ALLOWED',TG_NAME, TG_OP;
        END IF;

        IF (new.id > 0) THEN

        select pg_advisory_lock(1010) INTO footmp;
        select COALESCE(max(id),0)+1 INTO nuid FROM payments_finalization;
        select pg_advisory_unlock(1010) INTO footmp;

        NEW.id := nuid ;
        END IF;

        RETURN NEW;

END;
$function$
amantzio@[local]/dynacom=# \sf payments_finalization_set_epayment_finalized
CREATE OR REPLACE FUNCTION 
public.payments_finalization_set_epayment_finalized()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
tmp int;
REC RECORD;
BEGIN

        IF (TG_WHEN != 'AFTER') THEN
                RAISE EXCEPTION 'TRIGGER : % supports only ON AFTER. 
Called on unsuported WHEN : %',TG_NAME, TG_WHEN;
        END IF;

        IF (TG_OP = 'INSERT') THEN
                UPDATE payment p SET isfinalized = 't', status = 'FNLZ' 
WHERE p.year=NEW.year AND p.doc_no=NEW.doc_no AND p.is_epayment=0;
        ELSIF (TG_OP = 'DELETE') THEN
                UPDATE payment p SET isfinalized = 'f', status = 'INSD' 
WHERE p.year=OLD.year AND p.doc_no=OLD.doc_no AND p.is_epayment=0;
        ELSE
                /* UPDATE */
                RAISE EXCEPTION 'TRIGGER : % called on unsuported op : 
%',TG_NAME, TG_OP;
        END IF;

/* */
        RETURN NEW;
END;
$function$
amantzio@[local]/dynacom=# \sf payments_finalization_force_integrity
CREATE OR REPLACE FUNCTION public.payments_finalization_force_integrity()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
tmpingroup int;
tmp int;
REC RECORD;
initid INT;
BEGIN
        IF (NEW.id > 0 AND pg_trigger_depth() = 1) THEN
                SET CONSTRAINTS ALL DEFERRED;
                SELECT COALESCE(max(id),0)+1 into initid FROM 
payments_finalization WHERE sign_list;
                tmp := 0;
                FOR REC IN SELECT year,doc_no FROM payments_finalization 
WHERE NOT sign_list ORDER BY 
bank_name,management_company_name,beneficiary_name,year,doc_no DESC LOOP
                        UPDATE payments_finalization SET id=initid+tmp 
WHERE year=REC.year AND doc_no=REC.doc_no;
                        tmp := tmp + 1;
                END LOOP;
        END IF;
        RETURN NEW;
END;
$function$
amantzio@[local]/dynacom=#

How I replicated : (all tested in PgSQL 18beta1)

session A)

amantzio@[local]/dynacom=# begin; select txid_current(), 
pg_backend_pid() ;  UPDATE payments_finalization pf set sign_list = true 
where delivered_at::date = current_date ;
BEGIN
txid_current | pg_backend_pid
--------------+----------------
    117269038 |          16941
(1 row)

UPDATE 6
amantzio@[local]/dynacom=*#

session B)

postgres@[local]/dynacom=# begin; select txid_current(), 
pg_backend_pid() ;  UPDATE payments_finalization pf set sign_list = true 
where delivered_at::date =
current_date ;
BEGIN
txid_current | pg_backend_pid
--------------+----------------
    117269039 |          16952
(1 row)

(..waits..)

session C)

amantzio@[local]/dynacom=# begin; select txid_current() , 
pg_backend_pid()  ; INSERT INTO payments_finalization (year, doc_no, 
accnt_ukey, inserted_at, bank_name, management_company_name,
beneficiary_name, currency, amount, explanation, card_code, vsl_code, 
signed_by, delivered_at, group_explanation, ingroup, is_transfer, 
bank_bic, bank_account, amount_local, creditor_bank_
name, creditor_bank_bic, creditor_bank_account, sign_list) VALUES (2025, 
395302, 143392502, '2025-08-13 01:00:00+03', 'CREDIT SUISSE AG', 
'0006-DYNACOM TANKERS MANAGEMENT LTD', 'AUTUMN SHI
PPING SERVICES LIMITED', 'EUR', 500, 'TRANSFER TO EUROBANK GR / AUTUMN 
SHIP. E - OCEANIA', NULL, NULL, 'GP', '2025-08-13 11:55:28.359485+03', 
NULL, false, true, 'CRESCHZH', '08352333263820
01', 500, NULL, 'ERBKGRAA', '0026.0029.27.0200765876', false);
BEGIN
txid_current | pg_backend_pid
--------------+----------------
    117269040 |          16960
(1 row)

(..waits..)

session A)

amantzio@[local]/dynacom=*# rollback ;
ROLLBACK
amantzio@[local]/dynacom=#

session B)

UPDATE 6
postgres@[local]/dynacom=*#

session C)

ERROR:  deadlock detected
DETAIL:  Process 16960 waits for ShareLock on transaction 117269039; 
blocked by process 16952.
Process 16952 waits for ShareLock on transaction 117269040; blocked by 
process 16960.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (9611,12) in relation 
"payments_finalization"
SQL statement "UPDATE payments_finalization SET id=initid+tmp WHERE 
year=REC.year AND doc_no=REC.doc_no"
PL/pgSQL function payments_finalization_force_integrity() line 30 at SQL 
statement

Two workarounds - solutions I found :

1) If I replace the advisory lock in 
public.payments_finalization_set_id() with :

LOCK TABLE payments_finalization IN SHARE ROW EXCLUSIVE MODE;

Then apparently all inserts and updates are serialized , and this seems 
to do the trick.

2) Also, If i keep the advisory locks (no table locking) , but change 
all updates so that they perform row level locking , by

a) changing public.payments_finalization_force_integrity()'s loop to

                FOR REC IN SELECT year,doc_no FROM payments_finalization 
WHERE NOT sign_list ORDER BY 
bank_name,management_company_name,beneficiary_name,year,doc_no FOR 
UPDATE LOOP
                        UPDATE payments_finalization SET id=initid+tmp 
WHERE year=REC.year AND doc_no=REC.doc_no;
                        tmp := tmp + 1;
                END LOOP;

b) *AND* changing the other session updates to

with tempqry as (select year,doc_no from payments_finalization where 
delivered_at::date = current_date order by
bank_name,management_company_name,beneficiary_name,year,doc_no FOR UPDATE )
UPDATE payments_finalization pf set sign_list = true FROM tempqry WHERE 
pf.year = tempqry.year and pf.doc_no = tempqry.doc_no ;

also works and no deadlock is caused. (added consistent ordering in all 
updates as well, dont know if this has any effect). What is certain is 
that consistent ordering alone without the FOR UPDATE row level lock 
does not work, still causes the deadlock.

The thing is, that while I think I can solve this particular problem, I 
still don't understand why it happened.  I have some questions and 
remarks. Regarding the docs on locking 
(https://www.postgresql.org/docs/18/explicit-locking.html) , I found for 
instance that SHARE UPDATE EXCLUSIVE MODE does not block INSERTs, 
UPDATEs, whereas SHARE ROW EXCLUSIVE MODE blocks INSERTs and UPDATEs. It 
would help if those behaviors were documented, and also explain how does 
default implicit locking via MVCC interact with TABLE level locks. And 
the most important question is about transactionid-type locks,  while 
pg_locks's doc state that locktype can be "transactionid"  reading here 
: https://www.postgresql.org/docs/18/xact-locking.html didn't help much 
to understand the mechanics behind it, so while technically I see the 
deadlock pattern between pids 16960 and16952 andtransactions : 117269040 
and117269039 I cannot see where those transactionid-type ShareLock locks 
are acquired / requested for , in which part of the code / sql and why.

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-08-14T14:39:35Z

    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.

I have some questions:

1) Did this work in versions prior to 18?

2) The test case you ran was done on 18beta1, are you planning to test 
on the just released 18beta3?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-14T15:01:12Z

    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 even 
in the common deadlock scenario (xact A updating object 1 and then 2, 
while xact B updating 2 and then 1) the message is again the same , i.e.

Process <pid1> waits for ShareLock on transaction <xactB>; blocked by 
process <pid2>.

Process <pid2> waits for ShareLock on transaction <xactA>; blocked by 
process <pid1>.

while updating tuple ()...

Also I should have mentioned that it takes at least three transactions 
as in the example to make the deadlock happen. At least two of the 
"UPDATE" style and one of the "INSERT" style.

> I have some questions:
>
> 1) Did this work in versions prior to 18?
No, our production is on 16.9 and this is where I got the issue.
>
> 2) The test case you ran was done on 18beta1, are you planning to test 
> on the just released 18beta3?
I must upgrade, but I don't think anything will change, this behavior 
seems consistent at least across 16->18beta1
>
>

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-20T13:59:05Z

    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 
> even in the common deadlock scenario (xact A updating object 1 and 
> then 2, while xact B updating 2 and then 1) the message is again the 
> same , i.e.
>
> Process <pid1> waits for ShareLock on transaction <xactB>; blocked by 
> process <pid2>.
>
> Process <pid2> waits for ShareLock on transaction <xactA>; blocked by 
> process <pid1>.
>
> while updating tuple ()...
>
> Also I should have mentioned that it takes at least three transactions 
> as in the example to make the deadlock happen. At least two of the 
> "UPDATE" style and one of the "INSERT" style.
>
>> I have some questions:
>>
>> 1) Did this work in versions prior to 18?
> No, our production is on 16.9 and this is where I got the issue.
>>
>> 2) The test case you ran was done on 18beta1, are you planning to 
>> test on the just released 18beta3?
> I must upgrade, but I don't think anything will change, this behavior 
> seems consistent at least across 16->18beta1
Hi, I just tested with 18beta3, as expected, no change at all, I still 
get the deadlock.
>>
>>

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-25T14:40:06Z

    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 
>> even in the common deadlock scenario (xact A updating object 1 and 
>> then 2, while xact B updating 2 and then 1) the message is again the 
>> same , i.e.
>>
>> Process <pid1> waits for ShareLock on transaction <xactB>; blocked by 
>> process <pid2>.
>>
>> Process <pid2> waits for ShareLock on transaction <xactA>; blocked by 
>> process <pid1>.
>>
>> while updating tuple ()...
>>
>> Also I should have mentioned that it takes at least three 
>> transactions as in the example to make the deadlock happen. At least 
>> two of the "UPDATE" style and one of the "INSERT" style.
>>
>>> I have some questions:
>>>
>>> 1) Did this work in versions prior to 18?
>> No, our production is on 16.9 and this is where I got the issue.
>>>
>>> 2) The test case you ran was done on 18beta1, are you planning to 
>>> test on the just released 18beta3?
>> I must upgrade, but I don't think anything will change, this behavior 
>> seems consistent at least across 16->18beta1
> Hi, I just tested with 18beta3, as expected, no change at all, I still 
> get the deadlock.

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.

>>>
>>>

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-08-25T14:58:23Z

    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?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-25T16:11:26Z

    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.

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-08-26T06:22:42Z

    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/

Yours,
Laurenz Albe

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-26T07:06:13Z

    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

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-08-26T07:38:30Z

    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.

>
>