Как возникает deadlock в БД?

Как возникает Deadlock в базах данных?

Deadlock (взаимная блокировка) — это ситуация в многопользовательской среде, когда две или более транзакции взаимно блокируют друг друга, каждая ожидает ресурс, захваченный другой, что приводит к бесконечному ожиданию. Это классическая проблема параллелизма, возникающая из-за конкуренции за ресурсы (обычно строки таблиц) при определенном порядке операций.

Основные условия возникновения deadlock

Для возникновения взаимной блокировки необходимы четыре условия (условия Коффмана):

1. Условие взаимного исключения (Mutual Exclusion): Ресурс (например, строка в БД) не может использоваться более чем одной транзакцией одновременно. Он либо захвачен, либо свободен.
2. Условие удержания и ожидания (Hold and Wait): Транзакция, удерживая хотя бы один ресурс (блокировку), запрашивает новые ресурсы, которые в данный момент удерживаются другими транзакциями.
3. Условие отсутствия принудительной выгрузки (No Preemption): Ресурс может быть освобожден только транзакцией, которая его удерживает, добровольно. Система не может принудительно забрать блокировку.
4. Условие циклического ожидания (Circular Wait): Существует круговая цепочка из двух и более транзакций, каждая из которых ожидает ресурс, удерживаемый следующей транзакцией в цепочке.

Типичный сценарий возникновения в SQL

Рассмотрим классический пример с двумя транзакциями (T1 и T2) и двумя строками (A и B) в таблице accounts.

-- Транзакция 1 (T1)                         -- Транзакция 2 (T2)
BEGIN;                                       BEGIN;

-- Шаг 1: Блокирует строку A (id=1)
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
                                            -- Шаг 2: Блокирует строку B (id=2)
                                            UPDATE accounts
                                            SET balance = balance + 50
                                            WHERE id = 2;

-- Шаг 3: Пытается заблокировать строку B (id=2).
-- Блокировка уже у T2 -> T1 переходит в режим ожидания.
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
                                            -- Шаг 4: Пытается заблокировать строку A (id=1).
                                            -- Блокировка уже у T1 -> T2 переходит в режим ожидания.
                                            UPDATE accounts
                                            SET balance = balance - 50
                                            WHERE id = 1;

-- DEADLOCK! T1 ждет T2, а T2 ждет T1.

В результате образуется цикл зависимостей: T1 -> (ждет B) -> T2 -> (ждет A) -> T1. СУБД обнаруживает этот тупик (обычно с помощью графа ожидания или таймаута) и принудительно прерывает одну из транзакций (жертву), чтобы разрешить ситуацию. Пользователь получает ошибку типа Deadlock found when trying to get lock; try restarting transaction.

Распространенные причины в приложениях

1. Разный порядок обновления записей. Самая частая причина. Как в примере выше: если бы обе транзакции обновляли строки в одинаковом порядке (сначала A, потом B), deadlock бы не возник.
2. Отсутствие индексов. Если условие WHERE в UPDATE или DELETE не использует индекс, СУБД может установить блокировку на уровне таблицы или большого количества строк, что резко повышает вероятность конфликта.
3. Длительные транзакции. Чем дольше транзакция удерживает блокировки, тем выше шанс, что другая транзакция столкнется с ними.
4. Конкурентные операции SELECT ... FOR UPDATE. Явные блокировки на чтение для последующего обновления создают те же риски, что и UPDATE.
5. Взаимодействие через внешние системы. Например, T1 обновила запись, отправила сообщение в очередь, T2 получила его и пытается обновить запись, которую все еще удерживает T1, но при этом T1 нужен результат работы T2 для завершения.

Как обнаружить и предотвратить

Обнаружение:

• Мониторинг СУБД: SHOW ENGINE INNODB STATUS в MySQL/InnoDB выводит подробный анализ последнего deadlock в разделе LATEST DETECTED DEADLOCK.
• Логирование ошибок уровня приложения.

Стратегии предотвращения:

• Универсальный порядок блокировки ресурсов. Всегда изменяйте связанные записи в одном и том же порядке во всем приложении (например, всегда по возрастанию id).
• Короткие транзакции. Держите транзакции настолько короткими, насколько это возможно. Выполняйте логические операции перед открытием транзакции или после.
• Правильные индексы. Обеспечьте использование индексов в условиях WHERE операций модификации, чтобы блокировались только нужные строки, а не вся таблица.
• Использование SELECT ... FOR UPDATE NOWAIT или SKIP LOCKED (если поддерживается СУБД). Позволяет немедленно получить ошибку или пропустить заблокированные строки, вместо входа в очередь ожидания.
• Оптимистические блокировки. Используйте версионирование (version столбец) или контрольные суммы вместо пессимистических блокировок на уровне БД.
• Повторные попытки (Retry Logic). На уровне приложения: при отлове ошибки deadlock можно выполнить повторную попытку транзакции с небольшой задержкой (экспоненциальная отсрочка).

Таким образом, deadlock — это системное явление, возникающее при совпадении нескольких условий конкурентного доступа. Его нельзя полностью исключить в высоконагруженных системах, но можно минимизировать риски за счет строгой дисциплины работы с транзакциями, оптимизации схемы данных и реализации механизмов обработки сбоев в коде приложения.