Природа взаимных блокировок (Deadlocks) в движке InnoDB

Взаимная блокировка (Deadlock) возникает, когда две или более параллельные транзакции удерживают эксклюзивные блокировки на часть данных и одновременно пытаются захватить блокировки на данные, уже удерживаемые другой транзакцией, образуя замкнутый циклический граф ожидания (Cycle in Wait-For Graph). Движок InnoDB непрерывно отслеживает такие состояния с помощью встроенного механизма Deadlock Detector. Обнаружив цикл, MySQL мгновенно прерывает одну из транзакций (выбирая транзакцию с наименьшим весом — ту, которая выполнила меньше изменений INSERT/UPDATE/DELETE), выполняет ее автоматический откат (ROLLBACK) и возвращает клиенту ошибку:

«ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction»

Бизнес-риски

Сбой фоновых очередей и пакетных обработок данных, сброс пользовательских сессий оформления заказов, рост задержек в работе базы данных при увеличении числа параллельных потоков.

Сравнение таймаута ожидания (1205) и дедлока (1213)

Регламент расследования и устранения взаимных блокировок

Сценарий 1: Включение постоянного логирования дедлоков в журнал MySQL

По умолчанию в выводе SHOW ENGINE INNODB STATUS отображается только один последний дедлок. Включите запись всех инцидентов в error.log:

# 1. Включение логирования на лету без перезапуска СУБД:
SET GLOBAL innodb_print_all_deadlocks = ON;

# 2. Персистентное сохранение в my.cnf:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_print_all_deadlocks = 1

# 3. Мониторинг появления дедлоков в реальном времени:
sudo tail -f /var/log/mysql/error.log | grep -A 30 "LATEST DETECTED DEADLOCK"

Сценарий 2: Анализ графа дедлока в SHOW ENGINE INNODB STATUS

Выполните команду в консоли базы данных:

SHOW ENGINE INNODB STATUS\G

В блоке ---LATEST DETECTED DEADLOCK найдите две секции: (1) TRANSACTION и (2) TRANSACTION. Проверьте:

• Какую блокировку удерживал поток 1 (HOLDS THE LOCK) и какую ожидал (WAITING FOR THIS LOCK TO BE GRANTED).
• Какую блокировку удерживал поток 2 и какую запрашивал.
• Какая транзакция была выбрана жертвой (WE ROLL BACK TRANSACTION (1)).

Сценарий 3: Архитектурные паттерны устранения дедлоков

• Строгий порядок сортировки при обновлении: Если два процесса обновляют строки с ID 5 и 10, оба процесса ОБЯЗАНЫ делать апдейты в порядке возрастания: сначала 5, затем 10.
• Использование транзакций с уровнем изоляции READ COMMITTED: Устраняет блокировки промежутков (Gap Locks), которые чаще всего вызывают взаимные дедлоки при вставках.
• Реализация механизма Retry в клиентском коде:

  // Паттерн автоматического перезапуска транзакции при дедлоке (PHP / PDO):
  $maxRetries = 3;
  $retryCount = 0;
  while ($retryCount < $maxRetries) {
      try {
          $pdo->beginTransaction();
          // Выполнение SQL-операций...
          $pdo->commit();
          break;
      } catch (PDOException $e) {
          $pdo->rollBack();
          if ($e->getCode() == '40001' || $e->errorInfo[1] == 1213) {
              $retryCount++;
              usleep(50000 * $retryCount); // Экспоненциальная пауза перед повтором
              continue;
          }
          throw $e;
      }
  }

Типовые ошибки администраторов

• Отключение встроенного детектора innodb_deadlock_detect: Отключение проверки дедлоков переводит систему в режим ожидания 50-секундных таймаутов, что при высоком трафике полностью замораживает пул потоков.
• Неявные Gap-блокировки при INSERT ... ON DUPLICATE KEY UPDATE: Данный запрос накладывает жесткие Next-Key блокировки на уникальные индексы, провоцируя частые дедлоки при параллельном исполнении.