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.
