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 and 16952 and transactions :
117269040 and 117269039 I cannot see
where those transactionid-type ShareLock locks are acquired /
requested for , in which part of the code / sql and why.
