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

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

Типичный сценарий на примере PostgreSQL:

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

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

Как с этим справляется СУБД:

1. Детектор взаимоблокировок периодически проверяет граф ожидания транзакций.
2. При обнаружении цикла СУБД принудительно откатывает (ROLLBACK) одну из транзакций (обычно ту, которую дешевле откатить, или которая пришла позже). Эта транзакция получает ошибку (например, ERROR: deadlock detected).
3. Оставшиеся транзакции могут продолжить выполнение.

Методы предотвращения:

• Упорядоченный доступ: Всегда блокировать ресурсы (например, строки таблицы) в одном и том же порядке (по возрастанию ID).
• Короткие транзакции: Минимизировать время удержания блокировок.
• Использование таймаутов: SET lock_timeout = '2s'; в PostgreSQL или настройки уровня изоляции.
• Оптимистичные блокировки: Использование версий строк (UPDATE ... WHERE id=1 AND version=5) вместо долгих пессимистичных блокировок.