SQL-запросы, которые убивают ваш прод: учимся ловить и лечить Deadlocks

[OlegIct]

Deadlock — это когда две транзакции держат друг друга за горло и ждут

а три, четыре могут?

Для финансовых операций, где важна консистентность — REPEATABLE READ или даже SERIALIZABLE, но с готовностью ретраить.

в Oracle Databse нет REPEATABLE READ, она хуже подходит для финансовых операций?

[ScriptShaper]

а три, четыре могут?

Да, deadlock может быть циклом любой длины. A ждёт B, B ждёт C, C ждёт A — классика. PostgreSQL детектит циклы любой глубины, просто на практике чаще встречаются парные.

в Oracle Database нет REPEATABLE READ, она хуже подходит для финансовых операций?

Oracle по умолчанию использует snapshot isolation (READ COMMITTED со снапшотами), что по поведению ближе к REPEATABLE READ PostgreSQL. Плюс у Oracle есть SERIALIZABLE. Так что для финансов Oracle подходит отлично, просто терминология другая. Не хуже, просто другая.

[OlegIct]

Oracle по умолчанию использует snapshot isolation (READ COMMITTED со снапшотами), что по поведению ближе к REPEATABLE READ PostgreSQL

есть ли пример, чем ближе? Разве PostgreSQL не использует снэпшоты на READ COMMITED:

postgres=# begin isolation level read committed;
BEGIN
postgres=*# select pg_current_snapshot();
 pg_current_snapshot 
---------------------
 225918:225918:
(1 row)

Для финансовых операций, где важна консистентность — REPEATABLE READ или даже SERIALIZABLE, но с готовностью ретраить

готов ретраить. Пишу форму с финансовой проводкой и кнопку "послать платеж". Формируется транзакция, я, следуя вашему совету, меняю на SERIALIZABLE, у меня вместо успешной фиксации транзакции сыпятся ошибки could not serialize access, но я "ретраю" (повторяю транзакцию заново) - 10, 100, 1000 раз. За время ретраев (секунды, минуты, часы) выключется питание, база данных и сервер приложений перегружаются. Где должна храниться и в каком виде информация, чтобы после рестарта ретраи продолжились?

Параллельно тот, кто послал финансовую проводку, смотрит на счета (откуда списывает и куда посылает) и не видит изменений спустя минуту, час пока идут ретраи. Посылает вторую транзакцию, она тоже ретраится. Потом они отретраиваются и получается две проводки. Такое, кстати, было в Альфобаке - посылаешь проводку, а баланс на счету не меняется примерно час, никаких следов, что посылал проводку в программе-клиенте нет. Думаешь то ли послал, то ли нет. Посылаешь вторую. Спустя час первая отрабатывает, а на вторую, хорошо что баланса не хватило, а то бы второй раз перевелось. Сейчас вроде платежи быстро проходят и вторую платежку не надо посылать.

[avs24rus]

Поясните, плиз, вот это: >Второй процесс тоже должен начинать с id = 1, даже если списывает с id = 2.

[ScriptShaper]

Имею в виду порядок захвата блокировок, не порядок бизнес-операции.

Если переводим деньги с id=2 на id=1, всё равно сначала блокируем id=1 (меньший), потом id=2. Списание/зачисление, потом, в любом порядке. Главное, блокировки всегда брать по возрастанию id. Тогда два процесса не смогут взять блокировки крест-накрест.

[vmorsk]

Прописная истина - не держать баланс в одной строке, а вести отдельные таблицы транзакций, где баланс является сводной суммой 😅

[ScriptShaper]

Справедливо. Append-only ledger + баланс как SUM(amount) правильный паттерн для финансов. Никаких UPDATE, только INSERT, никаких deadlock на балансе.

Но статья про общий случай, UPDATE'ы в реальных проектах никуда не деваются. Не всё финансы, и не везде есть ресурсы переписать на event sourcing. А грабли с блокировками универсальные.

[pae174]

Почему это стреляет на проде, а не на тестах

Потому что у вас там хреновые тесты.

[ScriptShaper]

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

[IAmAleksei]

Коллеги, приходилось ли вам создавать собственные автоматизированные инструменты для контроля долгих ретраев, «зависших» транзакций или аномалий согласованности? Если да — как они устроены и какие проблемы помогли решить?