From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | trafdev <trafdev(at)mail(dot)ru>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements |
Date: | 2016-07-02 18:14:11 |
Message-ID: | dfadb7f9-a78f-7488-9e22-1e0aed2fb74a@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | trafdev | 2016-07-02 18:38:54 | Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements |
Previous Message | trafdev | 2016-07-02 16:54:27 | deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements |