Как бороться с deadlock?

Question

[NubasLol]

Пример ошибки

SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
DETAIL: Process 3199799 waits for ShareLock on transaction 28066780; blocked by process 3199816.
Process 3199816 waits for ShareLock on transaction 28066777; blocked by process 3199799.
HINT: See server log for query details.
CONTEXT: while updating tuple (78846,29) in relation "notifications" (SQL: update "notifications" set "is_read" = 1, "updated_at" = 2023-02-01 12:26:05 where "notifications"."client_id" = 126473 and "notifications"."client_id" is not null)

sql

update "notifications"
set "is_read"    = 1,
    "updated_at" = 2023 - 02 - 01 12:26:05
where "notifications"."client_id" = 126473 and "notifications"."client_id" is not null

Явно в транзакцию ничего не оборачиваю. И вот совершенно не понимаю, как это дебажить и фиксить. Помогите :(

Comments

[Пасечник Кузьмич]

Зачем у этого запроса кавычки вокруг имён таблиц и полей? И почему метка времени так записана?

[NubasLol]

Пасечник Кузьмич, это Laravel так генерирует sql

[Дмитрий]

А в логах чего? там вроде второй запрос должен быть

[Дмитрий Беляев]

Зачем у этого запроса кавычки вокруг имён таблиц и полей?

Пасечник Кузьмич, у Postgres синтаксис такой, двойные кавычки - это имена сущностей (пространств, таблиц, полей, типов и т.д.). Такое выделение гарантирует, что сущность, в названии которой могут быть нестандартные символы, не сломает запрос.
Притом это не строки, строки в Postgres строго в одинарных кавычках.

[tukreb]

Дмитрий Беляев, на самом деле рекомендуется писать без кавычек, как раз таки, чтобы не использовать случайно зарезервированные слова.

[NubasLol]

Дмитрий,

db_1                 | 2023-02-24 12:56:58.077 UTC [3243800] DETAIL:  Process 3243800 waits for ShareLock on transaction 32998866; blocked by process 3243801.
db_1                 | 	Process 3243801 waits for ShareLock on transaction 32998865; blocked by process 3243799.
db_1                 | 	Process 3243799 waits for ExclusiveLock on tuple (170620,1) of relation 24019 of database 23678; blocked by process 3243800.
db_1                 | 	Process 3243800: update "notifications" set "is_read" = $1, "updated_at" = $2 where "client_id" = $3
db_1                 | 	Process 3243801: update "notifications" set "is_read" = $1, "updated_at" = $2 where "client_id" = $3
db_1                 | 	Process 3243799: update "notifications" set "is_read" = $1, "updated_at" = $2 where "client_id" = $3

Это я попробовал убрать is not null проверку, не помогло

[Дмитрий]

NubasLol, а сколько записей надо обновить этим одним апдейтом?

[NubasLol]

Дмитрий, Ну там сколько непрочитанных уведомлений у юзера зависит, ну не больше 100 думаю

[NubasLol]

Дмитрий,

$userNotifications = auth()->user()->notifications();

if (!$request->input('read_all', false)) {
    $userNotifications->whereIn('id', $request->input('ids', []));
}

$userNotifications->update(['is_read' => true]);

На ларе так выглядит

[Дмитрий]

NubasLol, ага. И я так понимаю что запросов на апдейт одного пользователя может прилететь много.

Попробуйте

update "notifications"
set "is_read"    = 1,
    "updated_at" = 2023 - 02 - 01 12:26:05
where "notifications"."id" in ( select id from "notifications" where “client_id” = 126473 and "notifications"."client_id" is not null order by id for update);

На Хабре была статья про деадлоки при пакетных апдейтах. Причины там разобраны.

[NubasLol]

Дмитрий, попробую, спасибо

[Дмитрий]

NubasLol, я даж нашел. https://habr.com/ru/amp/post/567514/

[NubasLol]

Дмитрий, Можешь в ответы внести, сработало)

Answer 1

[Дмитрий]

update "notifications"
set "is_read"    = 1,
    "updated_at" = 2023 - 02 - 01 12:26:05
from ( select id from "notifications" where “client_id” = 126473 and "notifications"."client_id" is not null order by id for update) as t
where "notifications"."id" = t.id ;

Дедлоки при пакетных апдейтах.

Comments

[Сергей c0re]

а что здесь сильно изменилось? вот сами то статью читали? наверно надо ctid использовать?

[Дмитрий]

Сергей c0re, читал. "наверное надо использовать" для чего?

[Сергей c0re]

Дмитрий,

способ обновить уже найденные на предыдущем шаге записи без повторного поиска по индексам.

а что даст

where "notifications"."id" in ( select id from "notifications" where “client_id” = 126473 and "notifications"."client_id" is not null order by id for update);

м... в конце for update ... не доскроллил ))) хм...

[Дмитрий]

Сергей c0re, статью прочитайте абзац Упреждающая блокировка FOR UPDATE. Проблема с деадлоками здесь возникает здесь потому что строки для update постгрес будет выдавать в неопределенном порядке. и по этому может возникнуть ситуация когда первый запрос схватил 1 строку, и пытается получить вторую, а второй параллельный запрос схватит 2, и будет ждать 1 строку. по этому когда мы гвоздям упорядочиваем строки для апдейта - такой ситуации не будет. собственно и без lock update должно работать

З.Ы. Нахрена я ссылку на статью то привел?
З.З.Ы у меня сильное подозрение что использование ctid здесь перестраховка. Не говоря о том что я отвечал на вопрос с деадлоками - а уже потом искал статью с объяснениями почему оно так. Но судя по всему зря искал :)

[Сергей c0re]

Дмитрий, норм! интересно )

[Сергей c0re]

Дмитрий, хотя все равно недопонимаю, почему при упорядочивании такой ситуации не будет...
а если в обоих параллельных запросах идут айдишники 1, 2, 5, 10 ??

[Дмитрий]

Сергей c0re, запрос where id in (1, 2, 5, 10) без order by может вернуть 1,2,5,10. может вернуть 2,5,1,10. может вернуть 10,5,2,1 - ибо постгрес будет возвращать записи в том порядке в котором нашел. А искать он будет сначала в кеше, потом на диске. В одну секунду он в кеше увидел 1,2 во вторую 5,10. С диском тоже не все гладко - потому что mvcc и всякие оптимизации ни хрена не гарантируют порядок. Да и вообще понятие порядок отсутствует. Следовательно первый апдейт выборкой where in получил что сначала он ему надо изменить запись с id 1, потом с id 5, он лочит запись с id 1, правит, и пытается получить лок на запись c id 5. Второй запрос update получил сначала запись с id 5, лочит правит и пытается получить лок на запись с id 1. Deadlock.

В случае с select order by мы гарантируем что все апдейты у нас получат один порядок. Запрос 1 получает 1,2,5,10. Лочит запись с id 1, правит, лочит c id 2, правит....... Второй запрос получает 1,2,5,10 - пытается получить лок на запись с id 1 - видит что залочена - ждет. Deadlock нет. В статье даже картинка есть

[Сергей c0re]

Дмитрий, т.е. второй запрос будет ждать первый пока тот полностью отработает?

[Дмитрий]

Сергей c0re, да.

Answer 2

[mitya_k]

У тебя есть видимо множество update, которые пытаются обновить данную запись. Вариантов решения несколько:

• Переписать бизнес логику, чтобы update шли последовательно, а не параллельно
  
• На уровне приложения ловить быть готовым, что может прилететь ошибка deadlock и сделать повторный запрос или что-то другое в зависимости от бизнес-логики
  
• И самый костыльный вариант это увеличить deadlock_timeout - это время сколько postgres ждет, в случае блокировок. Я бы это использовал только, как временный вариант
  

Comments

[NubasLol]

У тебя есть видимо множество update

Это да, только вот это разные client_id . Но запросов к таблице прилично (около 1000 в минуту)