Как бороться с дедлоками в базе данных?

Борьба с дедлоками в базе данных

Дедлок (взаимная блокировка) — это ситуация, когда две или более транзакции блокируют друг друга, каждая ожидает освобождения ресурса, занятого другой. Это приводит к «зависанию» процессов. Борьба с дедлоками — комплексный процесс, включающий профилактику, обнаружение и устранение.

Основные причины дедлоков

• Непоследовательный порядок блокировок: Транзакции блокируют ресурсы (например, строки или таблицы) в разной последовательности.
• Длительные транзакции: Чем дольше транзакция держит блокировки, тем выше шанс конфликта.
• Отсутствие индексов: Полное сканирование таблицы (FULL SCAN) часто приводит к блокировке большего количества строк, чем необходимо.
• Конкурентные обновления «горячих» строк: Высокая конкуренция за одни и те же данные (например, счетчик или баланс).

Стратегии профилактики и борьбы

1. Унификация порядка блокировок

Самое эффективное правило: всегда блокировать ресурсы в одинаковой последовательности во всем приложении. Например, при обновлении двух счетов всегда сначала блокировать счет с меньшим id.

// ПРАВИЛЬНО: Унифицированный порядок
$accountIds = [$fromAccountId, $toAccountId];
sort($accountIds); // Всегда блокируем в порядке возрастания ID

foreach ($accountIds as $accountId) {
    $stmt = $pdo->prepare("SELECT * FROM accounts WHERE id = ? FOR UPDATE");
    $stmt->execute([$accountId]);
}

2. Сокращение времени удержания блокировок

• Проектирование коротких транзакций: Выполняйте только необходимую логику внутри транзакции. Выносите подготовку данных и некритичные операции (логирование, вызовы внешних API) за пределы транзакции.
• Использование оптимистичных блокировок: Вместо SELECT ... FOR UPDATE применяйте версионирование (version или updated_at).

// Оптимистичная блокировка через версию
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ?, version = version + 1 WHERE id = ? AND version = ?");
$stmt->execute([$amount, $accountId, $currentVersion]);

if ($stmt->rowCount() === 0) {
    // Конфликт: кто-то уже обновил запись. Повторить или откатить.
}

3. Правильное индексирование

Убедитесь, что все условия WHERE в запросах на обновление используют индексы. Это снижает количество блокируемых строк.

-- Без индекса по status будет блокировка многих строк
UPDATE orders SET status = 'shipped' WHERE status = 'processing';

-- Создание индекса
CREATE INDEX idx_orders_status ON orders(status);

4. Использование уровней изоляции и подсказок блокировок

• Понижение уровня изоляции: Например, использование READ COMMITTED вместо REPEATABLE READ (в PostgreSQL и SQL Server) уменьшает количество блокировок.
• Подсказки блокировок: В MySQL можно использовать FOR UPDATE NOWAIT (немедленная ошибка при невозможности блокировки) или FOR UPDATE SKIP LOCKED (пропуск заблокированных строк).

// SKIP LOCKED: выбрать только незаблокированные задачи
$stmt = $pdo->query("SELECT * FROM job_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT检 1");

5. Обнаружение и обработка

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

try {
    $pdo->beginTransaction();
    // ... критичные операции ...
    $pdo->commit();
} catch (\PDOException $e) {
    // Код ошибки дедлока в MySQL
    if ($e->getCode() == 40001 || strpos($e->getMessage(), 'Deadlock') !== false) {
        // ОБЯЗАТЕЛЬНО: Повторить транзакцию с задержкой
        usleep(rand(100000, 500000)); // Случайная задержка 0.1-0.5 секунды
        // retry logic
    } else {
        throw $e;
    }
}

6. Архитектурные подходы

• Очереди задач: Для фоновых операций используйте очереди (RabbitMQ, Kafka), обрабатывая задачи последовательно в одном воркере.
• Шардинг: Распределение «горячих» данных по разным узлам снижает конкуренцию.
• Альтернативные модели данных: Например, использование Event Sourcing или Командный Query Responsibility Segregation (CQRS) может устранить конкурентные обновления.

Мониторинг и анализ

Регулярно анализируйте логи дедлоков:

• MySQL: Включите innodb_print_all_deadlocks и смотрите логи. Используйте SHOW ENGINE INNODB STATUS.
• PostgreSQL: Смотрите записи в логе с deadlock_detected.
• Профилирование: Используйте APM.

Вывод

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