Что такое deadlock (взаимная блокировка) в SQL?

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

Классический пример:

-- ТРАНЗАКЦИЯ A
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Блокирует запись id=1
-- Транзакция A ждет блокировки на id=2...

-- ТРАНЗАКЦИЯ B (выполняется параллельно)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- Блокирует запись id=2
-- Транзакция B ждет блокировки на id=1...

-- Теперь A ждет B, а B ждет A. Deadlock!
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Транзакция A (ждет)
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Транзакция B (ждет)

Как СУБД решает проблему? Большинство СУБД (SQL Server, PostgreSQL, Oracle) имеют детектор взаимных блокировок, который периодически проверяет систему на наличие циклических зависимостей. При обнаружении deadlock:

1. СУБД выбирает одну транзакцию в качестве "жертвы" (обычно ту, которую дешевле откатить).
2. Завершает её с ошибкой (например, ERROR 1213 (40001): Deadlock found в MySQL).
3. Все изменения "жертвы" откатываются, и её блокировки снимаются.
4. Другие транзакции в deadlock могут продолжить выполнение.

Как предотвращать deadlock на практике?

1. Унифицированный порядок доступа: Всегда блокируйте ресурсы (например, строки таблиц) в одном и том же порядке во всех транзакциях. В примере выше, если бы обе транзакции сначала обновляли id=1, а потом id=2, deadlock бы не возник.
2. Короткие транзакции: Выполняйте как можно меньше операций в одной транзакции и завершайте её быстро.
3. Использование таймаутов: Установите SET LOCK_TIMEOUT 5000; (5 секунд). Если транзакция не может получить блокировку за это время, она прервется, не дожидаясь deadlock.
4. Изоляция READ COMMITTED SNAPSHOT (в SQL Server) или REPEATABLE READ (в некоторых СУБД): Эти уровни изоляции могут уменьшить количество блокировок, используя управление версиями строк.
5. Тщательный дизайн индексов: Отсутствие подходящего индекса может привести к блокировке всей таблицы вместо одной строки, увеличивая вероятность deadlock.