Postgresql - ›тупик от простого обновления. Я не могу понять причину

Вот таблица (упрощенная):

                                       Table "public.link"
    Column     |            Type             |                     Modifiers                     
---------------+-----------------------------+---------------------------------------------------
 id            | integer                     | not null default nextval('link_id_seq'::regclass)
 page_id       | integer                     | 
 placed_at     | timestamp without time zone | default now()
Indexes:
    "link_pkey" PRIMARY KEY, btree (id)
    "link_page_id_index" btree (page_id)
Foreign-key constraints:
    "link_page_id_foreign_key" FOREIGN KEY (page_id) REFERENCES page(id) ON UPDATE RESTRICT ON DELETE RESTRICT

А вот запрос (упрощенный):

UPDATE link SET page_id = ?, placed_at = now() 
WHERE id IN ( SELECT id FROM link ... ) AND page_id IS NOT NULL

Сообщение о взаимоблокировке:

ERROR: deadlock detected
  Detail: Process 5822 waits for ShareLock on transaction 19705; blocked by process 5821.
Process 5821 waits for ShareLock on transaction 19706; blocked by process 5822.
  Hint: See server log for query details.

Как этот запрос, выполняемый параллельно несколькими процессами, может привести к тупиковой ситуации?
Спасибо!


postgresql deadlock database-deadlocks
person Oleg Golovanov    schedule 12.10.2012    source источник
comment
Каковы ваши критерии отбора для внутреннего выбора?   -  person Germann Arlington    schedule 12.10.2012
comment
@Germann Arlington, это SELECT из ссылки с JOIN на другую таблицу. Но есть ли в этом смысл? Выбор не может привести к тупику, проблема с обновлением.   -  person Oleg Golovanov    schedule 12.10.2012
comment
SELECT может быть заблокирован обновлением в зависимости от уровня изоляции транзакции. Если на ваш внутренний выбор влияет какой-либо из обновляемых столбцов, значит, у вас проблема. Кстати: зависимость может быть через JOIN, а не только через прямое предложение WHERE ... BTW: Почему вы вообще обновляете свой page_id? Это внешний ключ, и поэтому обновление может иметь более серьезные побочные эффекты.   -  person Germann Arlington    schedule 12.10.2012
comment
Сложно сказать без DDL и только упрощенных запросов.   -  person Craig Ringer    schedule 12.10.2012
comment
Для чего вы опускаете часть своего запроса? Пожалуйста, добавьте SQL и удалите ... все, что имеет значение. Также я предполагаю, что у вас одновременно работает несколько сеансов? Не будем играть в угадайку.   -  person Kuberchaun    schedule 12.10.2012

Ответы (2)


arrow_upward
14
arrow_downward

Сеанс A пытается обновить идентификаторы 10, 2, 30, 4, а сеанс B пытается с 40, 30, 20, 10

Они оба пытаются заблокировать соответствующие строки, готовые к обновлению, и A получает 10 и ожидает 30, а B получает 30 и ожидает 10. Тупик.

Ваша основная проблема заключается в том, что вы пытаетесь обновить (некоторые) одни и те же идентификаторы в параллельных транзакциях.

Не зная структуры вашей базы данных и того, что именно вы пытаетесь сделать, трудно предложить лучшее решение. Как правило, вы должны либо убедиться, что разные серверные ВМ не обновляют одни и те же строки, либо уменьшить время ожидания и просто повторить попытку после случайной паузы.

person Richard Huxton    schedule 12.10.2012
comment
Если это окажется так (трудно сказать, учитывая чрезмерно упрощенный вопрос), то обходным путем может быть использование SELECT ... ORDER BY ... FOR UPDATE для получения блокировок строк в детерминированном порядке и только затем выполнение UPDATE этих строк. - person Craig Ringer; 12.10.2012
comment
@ Ричард Хакстон. Вы ошибаетесь. Я не пытаюсь обновлять ссылки с одинаковыми идентификаторами, посмотрите предложение WHERE: AND page_id IS NOT NULL. - person Oleg Golovanov; 12.10.2012
comment
@ Олег - конечно, да. Как вы думаете, может ли сеанс B заглянуть в будущее и увидеть, что сеанс A обновит те же строки, что и он? Ваши обновления page_id не будут видны B до тех пор, пока A не зафиксирует (и, возможно, позже, в зависимости от изоляции транзакции). - person Richard Huxton; 12.10.2012
comment
@Oleg PostgreSQL не заходит в тупик сам по себе. Очевидно, что здесь задействованы два сеанса: один с PID 5821, другой с PID 5822. Попробуйте записать SELECT pg_backend_pid() из вашего приложения, чтобы найти другую (успешную) сторону. - person willglynn; 12.10.2012
comment
@RichardHuxton, спасибо за отличный ответ! Никогда не думал, что там может случиться тупик :) - person Oleg Golovanov; 15.10.2012
comment
С параллелизмом всегда сложно справиться, даже если вы знаете, что может случиться. - person Richard Huxton; 15.10.2012

arrow_upward
1
arrow_downward

В большинстве случаев тупик возникает из-за кругового ожидания между строками, которые будут обновляться, поэтому, если вы хотите разрешить тупик, вы можете просто использовать упорядочение строк, которые вы хотите обновить.

UPDATE link SET page_id = ?, placed_at = now() 
WHERE id IN ( SELECT id FROM link ... order by page_id ) AND page_id IS NOT NULL
person Anurag jain    schedule 06.01.2016