Deadlock при параллельном переводе бонусов между счетами
Перевод бонусов между счетами выполняется двумя UPDATE внутри одной транзакции. Два параллельных перевода задевают одни и те же две строки, но идут по ним в разном порядке:
-- транзакция A (перевод 1 -> 2)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- транзакция B (перевод 2 -> 1)
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
В логах регулярно появляется Deadlock found when trying to get lock; try restarting transaction. Что здесь верно?
- Взаимная блокировка возможна только при явном LOCK TABLES: обычные UPDATE внутри транзакции строчных блокировок не ставят, поэтому причину надо искать в настройках соединения
- Ошибка означает, что СУБД не хватило памяти под структуру блокировок; помогает увеличение буферного пула и оперативной памяти на сервере базы
- Достаточно повысить уровень изоляции до SERIALIZABLE: на нём транзакции выстраиваются в очередь и взаимные блокировки не возникают в принципе
- Транзакции захватывают одни и те же строки в противоположном порядке и ждут друг друга; СУБД снимает одну из них. Лечится единым порядком обновления строк и повтором отменённой транзакции