Обсуждение: Strange deadlock with object/target of lock : transaction
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.
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
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:No, our production is on 16.9 and this is where I got the issue.
1) Did this work in versions prior to 18?
I must upgrade, but I don't think anything will change, this behavior seems consistent at least across 16->18beta1
2) The test case you ran was done on 18beta1, are you planning to test on the just released 18beta3?
On 8/14/25 16:01, Achilleas Mantzios wrote:
Hi, I just tested with 18beta3, as expected, no change at all, I still get the deadlock.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:No, our production is on 16.9 and this is where I got the issue.
1) Did this work in versions prior to 18?I must upgrade, but I don't think anything will change, this behavior seems consistent at least across 16->18beta1
2) The test case you ran was done on 18beta1, are you planning to test on the just released 18beta3?
On 8/20/25 14:59, Achilleas Mantzios wrote:
On 8/14/25 16:01, Achilleas Mantzios wrote:
Hi, I just tested with 18beta3, as expected, no change at all, I still get the deadlock.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:No, our production is on 16.9 and this is where I got the issue.
1) Did this work in versions prior to 18?I must upgrade, but I don't think anything will change, this behavior seems consistent at least across 16->18beta1
2) The test case you ran was done on 18beta1, are you planning to test on the just released 18beta3?
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.
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
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.
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
On 8/26/25 07:22, Laurenz Albe wrote:
Thanks Laurenz, will definitely read it.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
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: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.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:First off, I maybe wrong with the above conclusion, I noticed thatHi 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.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?
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.