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

On 07/02/2016 11:38 AM, trafdev wrote:
> 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 ...
>                 ",,,""

Best guess you are running into what is described here:

https://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-DEADLOCKS

Both transactions are holding locks on rows in T1 that the other wants also.

I may be missing something, but I am not sure why it is necessary to run
both sessions concurrently? Could you not do session1 and once it
completes then session2?

>
>
> 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!
>> >
>> >
>>
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com