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

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

Определение

Deadlock (взаимоблокировка) — это состояние, когда две или более транзакции ждут друг друга, создавая циклическую зависимость. Ни одна из них не может продолжить работу, система зависает.

Транзакция A заблокировала ресурс 1 и ждёт ресурса 2
Транзакция B заблокировала ресурс 2 и ждёт ресурса 1

=> Циклическое ожидание => DEADLOCK

Классический пример

-- ТРАНЗАКЦИЯ A (Connection 1)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Блокирует строку 1
WAIT FOR 2 seconds...  -- Имитируем задержку
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Ждёт разблокировки строки 2

-- ТРАНЗАКЦИЯ B (Connection 2) - запустить параллельно
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- Блокирует строку 2
WAIT FOR 2 seconds...  -- Имитируем задержку
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- Ждёт разблокировки строки 1

Результат:

• Транзакция A: ждёт разблокировки строки 2 (которую держит B)
• Транзакция B: ждёт разблокировки строки 1 (которую держит A)
• Обе зависают
• База данных автоматически откатывает одну из них

Условия для deadlock

Для возникновения deadlock нужны ВСЕ 4 условия:

1. Mutual Exclusion — ресурсы могут быть использованы только одной транзакцией
2. Hold and Wait — транзакция держит ресурс и ждёт другой
3. No Preemption — нельзя отобрать ресурс у другой транзакции
4. Circular Wait — циклическая цепочка зависимостей

Пример на Python

import threading
import time
from sqlalchemy import create_engine, text

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

def transaction_a():
    with engine.connect() as conn:
        conn.execute(text("BEGIN"))
        # Блокируем счёт 1
        conn.execute(text("UPDATE accounts SET balance = balance - 100 WHERE id = 1"))
        time.sleep(2)  # Имитация задержки
        # Ждём разблокировки счёта 2
        conn.execute(text("UPDATE accounts SET balance = balance + 100 WHERE id = 2"))
        conn.execute(text("COMMIT"))

def transaction_b():
    with engine.connect() as conn:
        conn.execute(text("BEGIN"))
        # Блокируем счёт 2
        conn.execute(text("UPDATE accounts SET balance = balance - 100 WHERE id = 2"))
        time.sleep(2)  # Имитация задержки
        # Ждём разблокировки счёта 1
        conn.execute(text("UPDATE accounts SET balance = balance + 100 WHERE id = 1"))
        conn.execute(text("COMMIT"))

# Запускаем параллельно
thread_a = threading.Thread(target=transaction_a)
thread_b = threading.Thread(target=transaction_b)

thread_a.start()
thread_b.start()

thread_a.join()  # Одна из них получит DeadlockDetected
thread_b.join()

Результат:

pycopg2.errors.DeadlockDetected: deadlock detected

Как БД обнаруживает deadlock

Модерные БД (PostgreSQL, MySQL, Oracle) имеют Deadlock Detector:

1. Ведут граф ожидания (wait-for graph)
2. Ищут циклы в графе
3. Если цикл найден — откатывают одну из транзакций

PostgreSQL

В PostgreSQL deadlock откатывается автоматически:

-- Увидеть детали deadlock в логах
SELECT * FROM pg_locks
WHERE NOT granted;  -- Показывает ждущие блокировки

-- Найти конфликтующие транзакции
SELECT pid, usename, application_name, query 
FROM pg_stat_activity
WHERE state = 'active';

Как избежать deadlock

1. Упорядочить доступ к ресурсам

# ПЛОХО — риск deadlock
def transfer_funds(from_account, to_account, amount):
    session.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
                   [amount, from_account])
    time.sleep(0.1)  # Опасная задержка
    session.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                   [amount, to_account])

# ХОРОШО — всегда в одном порядке
def transfer_funds(from_account, to_account, amount):
    # Сортируем по ID, чтобы ВСЕГДА блокировать в одном порядке
    account_ids = sorted([from_account, to_account])
    
    for account_id in account_ids:
        if account_id == from_account:
            session.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
                           [amount, account_id])
        else:
            session.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                           [amount, account_id])

2. Использовать короткие транзакции

# ПЛОХО — долгая транзакция
def process_order(order_id):
    transaction.begin()
    order = db.query(Order).filter(Order.id == order_id).with_for_update().first()
    
    # ДОЛГИЕ операции
    result = external_api.call()  # Может занять 30 секунд!
    
    order.status = result
    transaction.commit()

# ХОРОШО — короткая критическая секция
def process_order(order_id):
    # Долгая операция ДО транзакции
    result = external_api.call()
    
    # Коротенькая транзакция
    transaction.begin()
    order = db.query(Order).filter(Order.id == order_id).with_for_update().first()
    order.status = result
    transaction.commit()

3. Использовать SELECT FOR UPDATE с NOWAIT

# Если не можем получить блокировку сразу — откатываем
from sqlalchemy import text

def transfer_funds(from_id, to_id, amount):
    session.execute(text("""
        UPDATE accounts 
        SET balance = balance - :amount 
        WHERE id = :account_id
        FOR UPDATE NOWAIT
    """), {"amount": amount, "account_id": from_id})

4. Использовать уровень изоляции READ_COMMITTED

# В PostgreSQL по умолчанию READ_COMMITTED
# Это более безопасно для deadlock

# Если использовать SERIALIZABLE — повышается риск
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Обработка deadlock в коде

from sqlalchemy.exc import DBAPIError
import psycopg2

def transfer_with_retry(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            session.begin()
            
            # Упорядочиваем доступ
            for account_id in sorted([from_id, to_id]):
                session.execute(text("""
                    SELECT 1 FROM accounts WHERE id = :id FOR UPDATE
                """), {"id": account_id})
            
            # Выполняем операцию
            session.execute(text("""
                UPDATE accounts SET balance = balance - :amount 
                WHERE id = :account_id
            """), {"amount": amount, "account_id": from_id})
            
            session.execute(text("""
                UPDATE accounts SET balance = balance + :amount 
                WHERE id = :account_id
            """), {"amount": amount, "account_id": to_id})
            
            session.commit()
            return True
            
        except DBAPIError as e:
            session.rollback()
            # Проверяем, это deadlock
            if "deadlock" in str(e).lower():
                if attempt < max_retries - 1:
                    print(f"Deadlock detected, retrying... (attempt {attempt + 1})")
                    time.sleep(0.1 * (attempt + 1))  # Exponential backoff
                    continue
            raise
    
    return False

Диагностика deadlock

-- PostgreSQL: найти waiting queries
SELECT 
    a.pid, a.query, a.state,
    l.locktype, l.database
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE a.state = 'active' AND l.granted = false;

-- MySQL: проглядеть последний deadlock
SHOW ENGINE INNODB STATUS\G

Вывод

Deadlock — критическая проблема в конкурентных системах:

• Возникает при циклических зависимостях между транзакциями
• БД автоматически откатывает одну из них
• Профилактика: упорядочить доступ к ресурсам, короткие транзакции, правильная изоляция
• Обработка: retry логика с exponential backoff
• Мониторинг: регулярно проверяй логи и метрики deadlock