Что такое deadlock в SQL?

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

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

Пример:

Представим две параллельные транзакции, каждая из которых пытается обновить две строки в таблице, но в обратном порядке:

-- Транзакция 1
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- Блокировка строки с id=1
-- ...некоторая задержка...
UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- Ожидание строки с id=2 (заблокирована Т2)
COMMIT;

-- Транзакция 2 (параллельно)
BEGIN;
UPDATE accounts SET balance = balance - 5 WHERE id = 2; -- Блокировка строки с id=2
-- ...некоторая задержка...
UPDATE accounts SET balance = balance + 5 WHERE id = 1; -- Ожидание строки с id=1 (заблокирована Т1)
COMMIT;

В этом сценарии Транзакция 1 ждет строку id=2, которая заблокирована Транзакцией 2. Транзакция 2 ждет строку id=1, которая заблокирована Транзакцией 1. Обе транзакции заблокированы и не могут продолжить.

Разрешение и предотвращение:

• Автоматическое разрешение СУБД: Большинство современных СУБД (PostgreSQL, MySQL, SQL Server) имеют механизмы обнаружения deadlock. Они автоматически выбирают одну из транзакций в качестве «жертвы» и отменяют её (rollback), позволяя другой транзакции завершиться. Отмененная транзакция должна быть повторена приложением.
• Предотвращение (Best Practices):
  • Последовательный порядок доступа: Всегда запрашивать блокировки на ресурсы в одном и том же, заранее определенном порядке.
  • Короткие транзакции: Минимизировать время удержания блокировок, делая транзакции максимально короткими.
  • Использование NOWAIT или таймаутов: В некоторых СУБД можно указать, чтобы транзакция не ждала блокировку, а сразу завершалась с ошибкой (SET LOCK_TIMEOUT в SQL Server, FOR UPDATE NOWAIT в PostgreSQL).
  • Уменьшение области блокировки: Блокировать только абсолютно необходимые ресурсы, а не целые таблицы или большие диапазоны строк.