public inbox for [email protected]
help / color / mirror / Atom feedStrange deadlock with object/target of lock : transaction
10+ messages / 3 participants
[nested] [flat]
* Strange deadlock with object/target of lock : transaction
@ 2025-08-14 07:07 Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-14 07:07 UTC (permalink / raw)
To: [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-14 14:39 ` Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2025-08-14 14:39 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; [email protected] <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
@ 2025-08-14 15:01 ` Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-14 15:01 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-20 13:59 ` Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-20 13:59 UTC (permalink / raw)
To: [email protected]
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.
>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-25 14:40 ` Achilleas Mantzios <[email protected]>
2025-08-25 14:58 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-26 06:22 ` Re: Strange deadlock with object/target of lock : transaction Laurenz Albe <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-25 14:40 UTC (permalink / raw)
To: [email protected]
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.
>>>
>>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-25 14:58 ` Adrian Klaver <[email protected]>
2025-08-25 16:11 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2025-08-25 14:58 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:58 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
@ 2025-08-25 16:11 ` Achilleas Mantzios <[email protected]>
2025-08-26 07:38 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-25 16:11 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected]
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:58 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-25 16:11 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-26 07:38 ` Achilleas Mantzios <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-26 07:38 UTC (permalink / raw)
To: [email protected]; +Cc: Achilleas Mantzios <[email protected]>
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.
>
>
Attachments:
[application/sql] test_deadlock_dump.sql (25.8K, 3-test_deadlock_dump.sql)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
@ 2025-08-26 06:22 ` Laurenz Albe <[email protected]>
2025-08-26 07:06 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2025-08-26 06:22 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Strange deadlock with object/target of lock : transaction
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Re: Strange deadlock with object/target of lock : transaction Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Re: Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-26 06:22 ` Re: Strange deadlock with object/target of lock : transaction Laurenz Albe <[email protected]>
@ 2025-08-26 07:06 ` Achilleas Mantzios <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Achilleas Mantzios @ 2025-08-26 07:06 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-08-26 07:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-14 07:07 Strange deadlock with object/target of lock : transaction Achilleas Mantzios <[email protected]>
2025-08-14 14:39 ` Adrian Klaver <[email protected]>
2025-08-14 15:01 ` Achilleas Mantzios <[email protected]>
2025-08-20 13:59 ` Achilleas Mantzios <[email protected]>
2025-08-25 14:40 ` Achilleas Mantzios <[email protected]>
2025-08-25 14:58 ` Adrian Klaver <[email protected]>
2025-08-25 16:11 ` Achilleas Mantzios <[email protected]>
2025-08-26 07:38 ` Achilleas Mantzios <[email protected]>
2025-08-26 06:22 ` Laurenz Albe <[email protected]>
2025-08-26 07:06 ` Achilleas Mantzios <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox