Блокировки и взаимоблокировки (deadlocks) в реляционных БД

Введение

Транзакции (статья 408) достигают изоляции друг от друга во многом через блокировки — в статье разберём механизм блокировок строк/таблиц, и взаимоблокировку (deadlock) — ситуацию, когда две или более транзакций блокируют друг друга циклически, требуя вмешательства СУБД для разрешения.

Концепция

Блокировка предотвращает одновременное, конфликтующее изменение (или, для более строгих блокировок, даже чтение) одних и тех же данных несколькими транзакциями параллельно — большинство современных СУБД используют построчные блокировки (row-level locking) по умолчанию для модификации данных, что позволяет разным транзакциям параллельно работать с разными строками одной таблицы без конфликта. Взаимоблокировка возникает, когда транзакция A удерживает блокировку, нужную транзакции B, а транзакция B одновременно удерживает блокировку, нужную транзакции A — обе транзакции бесконечно ждали бы друг друга без внешнего вмешательства, и СУБД автоматически обнаруживает такую циклическую зависимость, принудительно прерывая (откатывая) одну из конфликтующих транзакций, чтобы разрешить ситуацию для оставшейся.

Пример кода: Блокировки и взаимоблокировки

-- Явное блокирование строк для чтения с намерением последующего изменения
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- блокирует строку до конца транзакции
-- другие транзакции, пытающиеся изменить (или также SELECT ... FOR UPDATE) эту же строку,
-- будут вынуждены ждать до COMMIT/ROLLBACK этой транзакции
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Классический сценарий взаимоблокировки — две транзакции блокируют строки в РАЗНОМ порядке

-- Транзакция A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- блокирует строку id=1
-- ... в этот момент Транзакция B уже заблокировала строку id=2 ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- ЖДЁТ освобождения блокировки строки id=2

-- Транзакция B (выполняется параллельно):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- блокирует строку id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- ЖДЁТ освобождения блокировки строки id=1
-- РЕЗУЛЬТАТ: взаимоблокировка — СУБД обнаружит цикл и прервёт ОДНУ из двух транзакций с ошибкой

-- Решение: всегда блокировать строки в одном, согласованном порядке (например, по возрастанию id)
-- Транзакция A и Транзакция B обе теперь блокируют сначала id=1, затем id=2 — цикл невозможен
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- сначала меньший id
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- затем больший id
COMMIT;

Пояснения к коду

SELECT ... FOR UPDATE явно запрашивает блокировку выбранных строк сразу при чтении, до их фактического изменения через последующий UPDATE — это полезно для паттерна «прочитать значение, принять решение на основе прочитанного, затем изменить», когда важно гарантировать, что между чтением и последующим изменением никакая другая транзакция не успеет изменить те же данные (что без явной блокировки могло бы привести к принятию решения на основе уже устаревшего, изменившегося значения). Сценарий взаимоблокировки в примере показывает классическую причину — две транзакции пытаются заблокировать одни и те же две строки, но в противоположном порядке, создавая циклическую зависимость; решение через согласованный порядок блокировки (всегда сначала строку с меньшим id, независимо от того, в каком порядке логика приложения «естественно» хотела бы их обработать) — простой, но эффективный практический приём предотвращения этого класса проблем.

Подводные камни

• Игнорирование необходимости обработки ошибки взаимоблокировки в коде приложения — поскольку СУБД принудительно прерывает (откатывает) одну из конфликтующих транзакций для разрешения взаимоблокировки, код приложения, выполняющий транзакции, должен явно перехватывать соответствующую ошибку и обычно повторять прерванную транзакцию заново (аналогично подводному камню про повторение при SERIALIZABLE из статьи 408), а не считать это редкой, «необязательно обрабатываемой» ошибкой — для систем с реальной конкурентностью изменений взаимоблокировки рано или поздно происходят, и приложение должно быть готово к ним.
• Несогласованный порядок блокировки одних и тех же ресурсов в разных частях кодовой базы — даже если в одном месте кода явно соблюдается порядок «сначала меньший id» (как в примере), другая часть того же приложения (написанная другим разработчиком, или в другое время без знания об этом соглашении) может непреднамеренно нарушить этот порядок, восстанавливая риск взаимоблокировки; для крупных проектов стоит явно документировать и проверять (например, через код-ревью) соблюдение согласованного порядка блокировки во всех местах, где это применимо.
• Чрезмерно широкие или длительные блокировки (блокировка большого количества строк или удержание блокировки длительное время из-за медленной, не связанной с самой блокировкой работы внутри той же транзакции, статья 408) — это увеличивает как вероятность конфликтов с другими параллельными транзакциями, так и потенциальный масштаб последствий взаимоблокировки (откат более крупной, более «дорогой» по уже выполненной работе транзакции); блокировки должны охватывать минимально необходимый набор строк на минимально необходимое время.
• Использование блокировок на уровне всей таблицы там, где было бы достаточно построчных блокировок — явное блокирование целой таблицы (через LOCK TABLE или аналогичные конструкции) полностью устраняет параллелизм для всех операций с этой таблицей, что для высоконагруженных систем может стать серьёзным узким местом производительности; построчные блокировки (используемые по умолчанию большинством операций модификации в современных СУБД) дают значительно больше параллелизма, позволяя разным транзакциям одновременно работать с разными строками без конфликта.