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

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

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

Классический сценарий deadlock

Представь две транзакции:

Транзакция 1:
1. Блокирует строку A
2. Пытается заблокировать строку B → ЖДЁТ

Транзакция 2:
1. Блокирует строку B
2. Пытается заблокировать строку A → ЖДЁТ

Транзакция 1 ждёт, пока Транзакция 2 освободит B, но Транзакция 2 ждёт, пока Транзакция 1 освободит A. Получается замкнутый цикл — deadlock.

SQL пример deadlock

-- Сессия 1
BEGIN TRANSACTION;
UPDATE orders SET amount = 1000 WHERE order_id = 1;
-- ... блокирует строку с order_id = 1

UPDATE orders SET amount = 2000 WHERE order_id = 2;
-- ... пытается заблокировать order_id = 2 (может ждать)

-- Сессия 2 (одновременно)
BEGIN TRANSACTION;
UPDATE orders SET amount = 3000 WHERE order_id = 2;
-- ... блокирует строку с order_id = 2

UPDATE orders SET amount = 4000 WHERE order_id = 1;
-- ... пытается заблокировать order_id = 1 (ждёт!)

Результат: Deadlock. Одна из транзакций будет откачена (rolled back) СУБД.

Механизм обнаружения deadlock

СОУД обнаруживает deadlock несколькими способами:

1. Граф ожидания (Wait Graph) — СУБД отслеживает, какая транзакция ждёт какую. Если в графе обнаружен цикл → deadlock.

2. Таймаут — если транзакция ждёт дольше установленного времени, она откатывается.

3. Иерархия приоритета — некоторые СУБД откатывают менее приоритетную транзакцию.

Обработка deadlock в приложении

import time
from sqlalchemy import create_engine, text
from sqlalchemy.exc import DBAPIError

engine = create_engine("postgresql://user:pass@localhost/db")

def execute_with_retry(query, max_retries=3):
    for attempt in range(max_retries):
        try:
            with engine.connect() as conn:
                conn.execute(text(query))
                conn.commit()
                return True
        except DBAPIError as e:
            if "deadlock" in str(e).lower():
                if attempt < max_retries - 1:
                    wait_time = 2 ** attempt  # exponential backoff
                    print(f"Deadlock detected, retrying in {wait_time}s...")
                    time.sleep(wait_time)
                else:
                    raise
            else:
                raise
    return False

# Использование
execute_with_retry("UPDATE orders SET status = processed WHERE id = 1")

Стратегии избежания deadlock

1. Согласованный порядок доступа (Ordered Access)

Всегда обращаться к ресурсам в одном и том же порядке:

-- ✅ Правильно: всегда сначала orders, потом customers
BEGIN;
UPDATE orders SET ... WHERE id = 1;
UPDATE customers SET ... WHERE id = 1;
COMMIT;

2. Минимизация времени транзакции

-- ❌ Плохо: долгая транзакция
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PERFORM some_expensive_calculation();  -- долгая операция
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- ✅ Хорошо: быстрая транзакция
result = some_expensive_calculation();
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. Использование правильного уровня изоляции

-- PostgreSQL поддерживает разные уровни
BEGIN ISOLATION LEVEL READ COMMITTED;  -- более мягкий уровень
-- операции...
COMMIT;

4. SELECT FOR UPDATE с осторожностью

-- Может привести к deadlock, если порядок нарушен
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
SELECT * FROM customers WHERE id = 1 FOR UPDATE;
COMMIT;

5. Мониторинг и логирование

-- PostgreSQL: узнать о недавних deadlock
SELECT
    datname,
    deadlocks
FROM pg_stat_database
WHERE deadlocks > 0
ORDER BY deadlocks DESC;

Отличие от других проблем конкурентности

Ключевые выводы

• Deadlock — это циклическая зависимость между транзакциями
• СУБД автоматически откатывает одну из них
• Главная стратегия: согласованный порядок доступа к ресурсам
• Retry logic в приложении для обработки случайных deadlock
• Мониторинг частых deadlock — признак архитектурной проблемы