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

Question

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

Тип вопроса: Базы данных Вероятность: 10% Категория: Python Разработчик

Ответ

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).
- Уменьшение области блокировки: Блокировать только абсолютно необходимые ресурсы, а не целые таблицы или большие диапазоны строк.

Ответ 18+ 🔞

А, слушай, смотри, вот эта штука — deadlock, взаимная блокировка. Это когда твои транзакции в SQL так друг друга заблокируют, что все, пиздец, полный ступор. Представь: два чувака в узком коридоре, каждый хочет пройти, но оба уперлись и стоят. И ни один не может двинуться, потому что ждет, пока второй отъебётся. Вот и тут так же.

Почему это вылезает, эта хрень? А потому что транзакции начинают хватать данные в разном порядке, как последние распиздяи. Одна схватила строку А, вторая — строку Б. А потом первая лезет за строкой Б, а вторая — за строкой А. И оба сидят и ждут, пока другой отпустит. Тупик, блядь, ёпта!

Вот тебе наглядный пиздец:

Представь, две транзакции параллельно работают с таблицей accounts. Одна хочет перевести деньги с id=1 на id=2, а другая — с id=2 на id=1. И делают они это в разной последовательности, как идиоты.

-- Транзакция 1
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- Заблокировала строку id=1
-- ...тут она немного прихуела и замешкалась...
UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- А тут она ждёт, пока строка id=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, которую держит первая!
COMMIT;

И что получается? Транзакция 1 держит id=1 и хочет id=2. Транзакция 2 держит id=2 и хочет id=1. Они друг на друга смотрят, как два барана, и нихуя не происходит. Deadlock, сука! В рот меня чих-пых!

Как из этого дерьма вылезать и не попадать?

База сама разберётся, не парься: Нормальные СУБД (PostgreSQL, MySQL) — они не лысые. У них есть детектор deadlock'ов. Он смотрит на эту пьяную драку, выбирает одну транзакцию — «жертву» — и просто отменяет её (делает rollback). Вторая тогда спокойно завершается. Отменённую транзакцию надо будет запустить заново, вот и вся история. Удивление пиздец, да?
Чтобы не было мучительно больно, делай так (Best Practices, блядь):
- Порядок, сука, порядок! Всегда блокируй ресурсы в одном и том же, заранее придуманном порядке. Например, всегда сначала id с меньшим номером, потом с большим. Тогда одна транзакция схватит первое, вторая будет ждать, и никакого тупика.
- Быстро и без задержек. Делай транзакции короткими, как пинок под зад. Чем меньше времени держишь блокировку, тем меньше шансов, что кто-то в тебя врежется.
- Не жди, как лох. В некоторых базах можно сказать: «Не буду я ждать эту блокировку, пошла она нахуй!» (FOR UPDATE NOWAIT в PostgreSQL, SET LOCK_TIMEOUT в SQL Server). Транзакция сразу отвалится с ошибкой, и ты её перезапустишь — это лучше, чем висеть в deadlock'е.
- Хватай по минимуму. Не блокируй всю таблицу, если можно заблокировать только одну строку. Чем меньше схватил, тем меньше шансов с кем-то перепутаться.

Вот так-то, дружок. А то будешь делать как попало — получишь deadlock и будешь сидеть, чесать репу, как Герасим из той истории, который только «Муму» сказать мог.