Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

Yes, you are right about sessions.

Here is the case from the server log:

"deadlock detected","Process 2588 waits for ShareLock on transaction
1939192; blocked by process 16399. Process 16399 waits for ShareLock on
transaction 1939195; blocked by process 2588.

Process 2588:
UPDATE T1
     SET ...
     FROM trans1_T_tmp
     WHERE ...

Process 16399: 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 ...;","See server log for query details.",,,"while updating tuple
(388225,15) in relation ""T1""","                UPDATE T1
     SET ...
     FROM trans1_T_tmp
     WHERE ...
                 ",,,""


or another one:



"deadlock detected","Process 71490 waits for ShareLock on transaction
2001693; blocked by process 71221. Process 71221 waits for ShareLock on
transaction 2001689; blocked by process 71490.
Process 71490: 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 ...;
Process 71221:                 UPDATE T1
     SET ...
     FROM trans1_T_tmp
     WHERE ...
                 ","See server log for query details.",,,"while updating
tuple (93716,27) in relation ""T1""","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 ...;
Process 71221:                 UPDATE T1
     SET ...
     FROM trans1_T_tmp
     WHERE ...",,,""



On 07/02/16 11:14, Adrian Klaver wrote:
> On 07/02/2016 09:54 AM, trafdev wrote:
> > 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:
>
> session1:
>
> This is actually one session with multiple transactions, at least if I
> am following correctly.
>
> Assuming you have a:
>
> BEGIN;
>
> here.
>
> >
> > 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:
>
> session2:
>
> >
> > 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...
>
> What do the logs show as the error message?
>
> >
> > 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!
> >
> >
>
>