Как работают дедлоки?

Question

[Александр Панков]

Можете простыми словами про блокировки пояснить?

Я записываю кучу данных батчами.
Бывает так, что записывается одно и то же в уникальный ключ, для этого есть on conflict do nothimg.
И я часто получаю дедлоки.

Как понял, потому что 2+ транзакции пытаюсь записать одно и то же значение уникального ключа, случается дедлок и проходит какая-то одна транзакция, чаще самая последняя, но мне так не подходит, у меня батч на 2000 строк, он отменяется весь при дедлоке.

Везде рекомендуют отсортировать записи и вставлять их в идентичном порядке в каждой из транзакций.

Но почему сортировка сработает, я не понимаю... (ps я не проверял пока что).
Типа Т1 вставляет "1" и Т2 вставляет в это же время "1".
Ну и что... и почему тут нет блокировки, чем это отличается от
Т1 вставлет "1"
Т2 вставляет "2"
Т1 вставлет "2"
Т2 вставляет "1"
не понимаю, можно на "котиках" объяснить?

Взял отсюда https://ru.stackoverflow.com/questions/1163202/dea...

Comments

[Everything_is_bad]

у тебя вопрос про конкретную субд или в общем для всех? определись уже с тегами

[Александр Панков]

У меня постгрес, но на сколько я понимаю принцип общий у всех теги для того чтобы зацепить больше экспертов Everything_is_bad,

[Everything_is_bad]

Александр Панков, ну тогда при чем тут mysql? или оставляй только постгрес, или ставь только более общий тег "базы данных"

[Александр Панков]

Everything_is_bad, сделано

[Дмитрий]

Я так понимаю решили поменять стэк MySQL на PostgresSQL? И тут же прилетело граблей?

[Александр Панков]

Дмитрий, вы правы

[Дмитрий]

Александр Панков, ну даю в последний раз совет - прекращайте скакать по инструментам не изучив хотя бы что то одно. И вы не знаете минусов и плюсов MySQL но уже поскакали собирать грабли других инструментов. Нет в программировании серебряной пули. MySQL не лучше PostgresSQL, а Postgres не хуже MySQL.

А по вопросу- гуглите unique constraint causes deadlock postgresql. Не хрен в разных потоках один и тот же ключ обновлять

[Александр Панков]

Дмитрий, звучит как угроз)
Ну последний так последний, спасибо еще раз.

[Дмитрий]

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

[Александр Панков]

Дмитрий, ок, смотрите, подписка бесплатна).

Постгрес был выбран для реализации задачи потому что умеет возвращать вставленные только что записи через returning id, что не умеет MySQL, а я эти идишники в редис удобно сразу же пишу. Это стало решающий фактором, те идеально ложилось на весь придуманный процесс парсинга

[Дмитрий]

Александр Панков, LAST_INSERT_ID чем вас не устроил?

[Александр Панков]

Дмитрий, невозможностью отдать коллекцию после вставки батча, да и не всегда нужно id возвращать, бывает и другую колонку

[Дмитрий]

Александр Панков, ну другую колонку вы вроде в инсерте должны вставлять? ну да ладно, вообщем чувствую шоу будет интересным.

Answer 1

[Сергей Соловьев]

Этот дедлок - проявление MVCC, механизма, чтобы несколько пользователей могли работать с БД одновременно.
Визуализировать эту ситуацию можно как перекрестные стрелки в зависимостях


Если мы говорим про postgres, то если ты обновил какую-то запись (кортеж), то неявно получаешь на нее (запись) блокировку. Когда кто-то другой хочет обновить эту же запись, то ждет ее освобождения.
В данном случае (визуализация), 1 процесс выполнял UPDATE X, Y а другой UPDATE Y, X, т.е. первый взял блокировку сначала на X потом на Y, а другой - сначала на Y потом на X. Первая блокировка успешна, но вторые нет - они уже заняты.
В данном случае, разрешить их нельзя, т.к. оба ждут пока один из них снимет блокировку, но этого не произойдет. Блокировка со строки снимется по окончании транзакции.

Совет про сортировку взят, чтобы привнести единообразие в работу. Грубо говоря, дедлок появляется, когда нет согласованности в работе. Если оба выполнят UPDATE X, Y (в таком порядке), то дедлока не будет т.к. один из них сможет взять все блокировки на строки успешно. Поведение второго будет уже зависеть от уровня сериализации:
- Read committed - обновит новые данные
- Другие (repeatable read, serializable) - ошибка выполнения
Это уже детали MVCC в postgres (read uncommitted нет)

Comments

[Александр Панков]

Сергей Соловьев , спасибо, но я все еще не догоняю. Вот конкретно мой пример
есть таблица с колонками id, root, key, value
на колонке root уникальный индекс

я вставляю туда строки, 1000 строк за раз при этом многократно (порядка 1млн записей в сумме), строки вставляются многоим клиентами и вставки могут содержать одинаковый root

такой запрос у меня

inset into .. (root, key, value) values (1,'a','b'), (2,'c','d').... on conflict root set update key .. value

те у меня прям куча инсертов
и в моей сиутации нет-нет да и случается дедлок ... while inseting index ....
каким образом мне тут помогает сортировка и почему вообще дедлок? я не мойму.

получается какой-то из клиентов генерит инсерт, случается конфлик индекса, срабатывает set update
и в это же время у другого клиента так же случается конфликт этого же root и срабатывает set update.
те 2 запроса обвноляют одну и то же строку.. но где делок, почему в порядке очереди это не срабаытывает?

где тут взаимная блокировка я не могу понять... отсотирую я данные по root в инсерте, а дальше-то как это мне помогает? помогите разжевать пожалуйста.

[Сергей Соловьев]

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

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

Можешь представить это как ребенок, который хочет выйти погулять, но ему запрещает мама:
1. Мама хочет, чтобы ДЗ было сделано, поэтому не пускает
2. Ребенок хочет встретиться с другом, чтобы сделать совместный проект для ДЗ

Мама получила блокировку на выход из дома, а теперь хочет на ДЗ. Ребенок получил блокировку на ДЗ (его выполняет), а теперь хочет получить блокировку на выход из дома. Вот тут дедлок, т.к. эта ситуация не разрешима - ДЗ в данном случае никогда не будет сделано. (Возможно плохой пример)

Почему советуют сортировать? Потому что в этом случае все будут свои действия координировать - есть глобальный порядок получения этих блокировок. Например, 3 кортежа с id 1, 2, 3 и 2 клиента, которые хотят обновить кортежи 1 и 3. Без сортировки мы можем получить дедлок как в примере выше - UPDATE 1, 3 и UPDATE 3, 1. Если сортировка будет, то уже не важно какие кортежи захотят изменять, т.к. кто-то один но сможет взять все блокировки:
- UPDATE 1, 3 и UPDATE 1, 3
- UPDATE 1, 2, 3 и UPDATE 2, 3
- UPDATE 1, 2, 3 и UPDATE 1, 2, 3

В случаях 1 и 3 кто-то очень быстрый получит блокировку на первый кортеж, а другой будет ждать всех остальных.
Во 2 случае, кортеж 1 никем не будет занят, поэтому его можно спокойно брать, но есть общие 2 и 3 - тут тоже проблем не будет, т.к. кто-то один успеет взять блокировку на 2, а другой будет ждать завершения его транзакции. В частности есть 2 исхода:
- Клиент 1 сначала берет блок. на 1, а после успевает взять 2. Тогда клиент 2, будет ждать завершения транзакции клиента 1 (хоть у него и меньше кортежей для обновления)
- Клиент 2 сначала бедет блок. на 2. Тогда клиент 1 успеет взять блок. на кортеж 1 (никем не занят), но заблокируется в ожидании кортежа 2 и не отвиснет пока транзакция клиента 2 не завершится (не будет снята блокировка)

PS.
Ты поставил ON CONFLICT .. SET ... - это никак не влияет на приобретение блокировок, это про конфликты нарушения ограничений. Скорее всего, ты имел ввиду SKIP LOCKED - он позволяет избежать этих дедлоков тем, что не берет блокировки. Но (!!) он только для SELECT, но не INSERT.
Чтобы его использовать можно сделать следующее:
1. BEGIN
2. SELECT ... FOR UPDATE SKIP LOCKED <---- Получаешь все не заблокированные на этот момент строки, но пропускаешь заблокированные (можно опустить SKIP LOCKED, но будешь ждать получения блокировок на них)
3. Делаешь манипуляции с данными
4. INSERT/UPDATE полученных ранее данных
5. COMMIT

Вот тут про блокировку строк расписано - https://habr.com/ru/companies/postgrespro/articles...

[Александр Панков]

Сергей Соловьев, спасибо, вы лучший