Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements
От | trafdev |
---|---|
Тема | Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements |
Дата | |
Msg-id | 71d88557-9820-e673-2dbc-0df6cd3de07c@mail.ru обсуждение исходный текст |
Ответ на | 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>)
|
Список | pgsql-general |
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! > > > > > >
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements
Следующее
От: Adrian KlaverДата:
Сообщение: Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements