deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements - Mailing list pgsql-general

From trafdev
Subject deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements
Date
Msg-id f9882e90-314e-d7b2-9a35-1b3e22d0f075@mail.ru
Whole thread Raw
Responses Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Hello.

I have two transactions (trans1 and trans2) updating tables T1 and T2 in
the same order, but in a different way.

trans1 creates temp table, copies data from a file and updates tables T1
and T2 from this temp table (using basic UPDATE form). It even commits
changes in between T1 and T2 updates to reduce locks.

trans2 creates two temp tables (T1_tmp and T2_tmp), copies data from
files and updates T1 and T2 using [WITH ... AS ... RETURNING] approach.
Unexciting rows should be inserted, existing updated (sum-ed with values
from temp tables). Both T1 and T2 must be updated in the same transaction.

There are no any FKs anywhere in these tables.



trans1:

DROP TABLE IF EXISTS trans1_T_tmp;

CREATE TABLE trans1_T_tmp (...);

COMMIT

COPY from FILE into trans1_T_tmp;

BEGIN
UPDATE T1
     SET ...
     FROM trans1_T_tmp
     WHERE ...
COMMIT

BEGIN
UPDATE T2
     SET ...
     FROM (SELECT ... FROM trans1_T_tmp)
     WHERE ...

DROP TABLE trans1_T_tmp;
COMMIT



trans2:

BEGIN

CREATE TABLE trans2_T1_tmp (...);
COPY from FILE into trans2_T1_tmp;
WITH agg_tmp AS (SELECT ... FROM trans2_T1_tmp GROUP BY ...), upd AS
(UPDATE T1 SET ... FROM agg_tmp s WHERE ... RETURNING ...) INSERT INTO
T1 (...) SELECT ... FROM agg_tmp s LEFT JOIN upd t ON (...) WHERE ...;
DROP TABLE trans2_T1_tmp;

CREATE TABLE trans2_T2_tmp (...);
COPY from FILE into trans2_T2_tmp;
WITH agg_tmp AS (SELECT ... FROM trans2_T2_tmp GROUP BY ...), upd AS
(UPDATE T2 SET ... FROM agg_tmp s WHERE ... RETURNING ...) INSERT INTO
T2 (...) SELECT ... FROM agg_tmp s LEFT JOIN upd t ON (...) WHERE ...;
DROP TABLE trans2_T2_tmp;

COMMIT

By an unknown [for me] reason trans1 and trans2 often produce deadlocks...

Could you explain what's the reason for that and how to avoid them?
And is there exist a better replacement for WITH ... AS ... RETURNING ?
Thanks!


pgsql-general by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: --EXTERNAL--Re: PSQL does not remove obvious useless joins
Next
From: Adrian Klaver
Date:
Subject: Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements