Deadlock и Lock wait timeout в MySQL: почему зависают операции сайта
Покупатель нажал «Оформить заказ», сотрудник одновременно изменил остаток, а фоновая интеграция обновила тот же товар. Страницы открываются, соединение с базой установлено, но отдельные операции ждут или завершаются ошибкой. Запрос mysql deadlock lock wait timeout часто приводит к одному совету: увеличить время ожидания. Однако сначала нужно понять, кто удерживает ресурс, что именно откатила база и можно ли безопасно повторить действие.
Ниже разобраны блокировки InnoDB в MySQL 8.4. Цель диагностики — восстановить завершение операций без частично сохранённых изменений и повторных списаний. Все ситуации с заказами, номерами товаров и временем условные; SQL приведён для чтения диагностических данных, а не для изменения рабочего сервера.
Чем deadlock отличается от долгого ожидания
При обычном ожидании транзакция Б хочет изменить строку, которую удерживает транзакция А. Если А завершится, Б сможет продолжить. Цикла здесь может вообще не быть: один обработчик просто слишком долго не фиксирует изменения. Ошибка Lock wait timeout exceeded означает, что ожидание достигло соответствующего предела, но сама по себе не объясняет, почему блокировка сохранялась.
При взаимной блокировке участники зависят друг от друга по кругу. Например, А удерживает ресурс, нужный Б, а Б — ресурс, нужный А. Такое определение и варианты конфликтов описаны в документации MySQL о deadlock. Сообщение Deadlock found when trying to get lock требует анализа нескольких транзакций, а не только запроса, получившего ошибку.
Для владельца сайта различие практическое: длительное ожидание увеличивает время ответа, а обнаруженный deadlock обычно прерывает одного из участников, чтобы остальные продвинулись. В обоих случаях приложение должно показать корректный результат. Исчезновение сообщения после обновления страницы ещё не подтверждает, что заказ записан полностью и ровно один раз.
Найдите настоящие границы транзакции
Начните с цепочки бизнес-действия: создание заказа, резервирование товара, применение скидки, запись задания на отправку уведомления. Отметьте, какие изменения должны существовать совместно. Затем найдите в коде начало транзакции, успешную фиксацию и все пути обработки исключений. Особенно полезно проверить ранний выход из функции и возврат соединения в пул после ошибки.
Согласно описанию autocommit в InnoDB, при включённом autocommit отдельный SQL обычно составляет собственную транзакцию. Но явный START TRANSACTION или BEGIN объединяет несколько операторов до завершения. Поэтому значение autocommit=1 само по себе не доказывает отсутствие открытой многооператорной транзакции.
Попросите разработчика описать границу человеческим языком: «либо заказ и его резерв появляются вместе, либо не появляется ничего». Такой контракт помогает оценивать обработчик ошибки. Если резерв записан одним автоматически фиксируемым запросом, а заказ другим, откат второго запроса не отменит первый. Это уже вопрос согласованности операции, который невозможно исправить единственной настройкой ожидания.
Проверяйте также административную панель, импорт и воркеры: они могут менять те же данные через совершенно другие участки кода.
Полезный вопрос для ревью: что произойдёт, если исключение возникнет после каждого отдельного шага? Для одного и того же заказа составьте ожидаемое состояние таблиц до начала, после успешной фиксации и после отмены. Это позволяет обнаружить скрытую фиксацию в библиотеке или обработчик, который записывает успешный статус раньше завершения обязательных изменений. Сопоставляйте ожидания с реальным поведением используемого драйвера и транзакционного механизма приложения.
Разберите конфликт на двух участниках
Учебный пример: две транзакции меняют остатки товаров 17 и 23. Обе должны обработать обе строки, но выбирают противоположный порядок. До начала опыта данные и количество товаров не имеют значения; существенны порядок захвата и незавершённость транзакций.
| Шаг | Транзакция А | Транзакция Б | Что получилось |
|---|---|---|---|
| 1 | Изменила товар 17 | Ещё не меняет данные | А удерживает блокировку 17 |
| 2 | Продолжает работу | Изменила товар 23 | Б удерживает блокировку 23 |
| 3 | Запросила изменение 23 | Ещё не завершилась | А ждёт Б |
| 4 | Ожидает | Запросила изменение 17 | Возник цикл ожиданий |
В другом учебном сценарии А изменила товар 17 и затем ждёт ответ внешней службы доставки. Б пытается изменить 17, но А ничего не ждёт от Б. Это длительная блокировка без описанного цикла. Исправление порядка строк не устраняет медленный внешний вызов внутри транзакции.
Реальный конфликт может затрагивать индексы и несколько таблиц, поэтому не выводите причину только из совпадающего номера заказа. Сохраните последовательности действий обоих участников. Запрос, на котором проявилась ошибка, нередко оказывается последним звеном, а блокировку создал более ранний оператор.
Определите, что именно откатила база
Самое опасное различие закреплено в правилах обработки ошибок InnoDB: deadlock приводит к откату всей выбранной транзакции, а lock wait timeout по умолчанию откатывает текущий оператор. При откате одного оператора блокировки транзакции не освобождаются автоматически. Включённый innodb_rollback_on_timeout меняет поведение timeout на откат всей транзакции.
| Событие | Что нельзя считать сохранённым | Что проверяет приложение |
|---|---|---|
| Deadlock | Все изменения транзакции-жертвы | Нужен ли повтор целой логической транзакции |
| Timeout при стандартном режиме | Изменения неудачного оператора | Осталась ли открытая транзакция с предыдущими изменениями |
| Timeout с полным откатом | Все изменения этой транзакции | Соответствует ли обработчик фактической настройке |
Представьте: обработчик сначала уменьшил остаток, затем не смог создать связанную запись резерва из-за ожидания. Если исключение поглощено и выполняется обычная фиксация, может сохраниться только первая часть операции. Для контракта «всё или ничего» безопасная проектная политика — явно отменить оставшуюся транзакцию и лишь затем решать вопрос повторения.
Это не утверждение, что MySQL всегда требует повторять всю операцию после timeout. Повтор отдельного SQL возможен в спроектированном сценарии. Но обработчик должен понимать сохранённое состояние, а не выбирать поведение по тексту ошибки наугад.
Проверьте настройки именно рабочего соединения
Пример запроса ниже только читает значения. Выполнять аналогичную диагностику следует через разрешённый доступ и по возможности в том соединении приложения, где возникает ошибка. Отдельная консоль может иметь другие параметры сессии.
SELECT
@@SESSION.autocommit AS session_autocommit,
@@SESSION.innodb_lock_wait_timeout AS row_wait_seconds,
@@GLOBAL.innodb_deadlock_detect AS deadlock_detection,
@@GLOBAL.innodb_rollback_on_timeout AS full_timeout_rollback;Справочник параметров InnoDB указывает, что innodb_lock_wait_timeout относится к ожиданию блокировок строк InnoDB и имеет глобальное и сессионное значения. Изменение глобального значения не переустанавливает параметр уже существующих соединений. innodb_rollback_on_timeout по умолчанию выключен и не является динамически изменяемым параметром.
Отдельно проверьте innodb_deadlock_detect. Когда обнаружение выключено, InnoDB полагается на механизм таймаута для разрешения таких конфликтов; это описано в разделе обнаружения deadlock. Тогда timeout не доказывает отсутствия цикла. Границу фактического отката всё равно оценивают с учётом настройки и поведения приложения.
Зафиксируйте значения вместе с именем экземпляра базы и временем наблюдения. Иначе легко сравнить ошибку рабочего узла с конфигурацией резервного или нового соединения.
Найдите ожидающего и удерживающего блокировку
В текущем конфликте нужны обе стороны: кто ждёт и кто мешает продвинуться. Таблица data_lock_waits показывает зависимости между запрошенными и удерживаемыми блокировками. Одна ожидающая транзакция может зависеть от нескольких блокирующих, поэтому одна строка результата не обязательно описывает весь конфликт.
Следующий пример связывает ожидание с описанием ресурсов. app_demo — условное имя схемы, которое заменяют известным именем приложения. Запрос не завершает соединения и не изменяет пользовательские данные.
SELECT
w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
r.OBJECT_SCHEMA, r.OBJECT_NAME, r.INDEX_NAME,
r.LOCK_TYPE, r.LOCK_MODE AS requested_mode,
b.LOCK_MODE AS blocking_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE = w.ENGINE
AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
ON b.ENGINE = w.ENGINE
AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA = 'app_demo'
LIMIT 20;Описание data_locks определяет поля объекта, индекса, режима и идентификатора блокировки. Пара «движок и идентификатор блокировки» нужна для корректного сопоставления. Идентификатор транзакции не следует путать с идентификатором соединения.
Это снимок меняющегося состояния. Пустой результат после завершения конфликта не опровергает предыдущую ошибку. Ограничение в 20 строк тоже не гарантирует полного охвата; при большом конфликте специалист исследует цепочку дальше.
Далее найдите в приложении владельца блокирующей транзакции и восстановите её действия с самого начала. Последний видимый SQL может отличаться от оператора, захватившего ресурс. Например, обработчик уже завершил изменение строки, но ещё не зафиксировал транзакцию и выполняет другой шаг. Поэтому объяснение «сейчас тяжёлого запроса нет» не исключает удержания блокировки. Для каждого участника нужны время начала, последний известный этап и ожидаемое условие завершения.
Сохраните доказательства завершившегося deadlock
Взаимная блокировка может исчезнуть раньше, чем администратор откроет диагностику: база уже выбрала жертву. Для последнего обнаруженного случая применяют команду чтения:
SHOW ENGINE INNODB STATUS;В разделе LATEST DETECTED DEADLOCK смотрят участников, удерживаемые и ожидаемые ресурсы, запросы и сведения об откате. Это последний обнаруженный deadlock, а не полный журнал всех конфликтов. Для серии событий администратор может согласовать временное включение innodb_print_all_deadlocks; такой способ записи в error log описан в рекомендациях MySQL по разбору deadlock.
Свяжите серверные сведения с журналом приложения: время с часовым поясом, идентификатор операции, версия обработчика, номер попытки, начало транзакции и результат её завершения. Удалите персональные значения перед передачей отчёта подрядчику, сохранив имена таблиц, порядок действий и устойчивые условные обозначения записей.
Не назначайте виновником транзакцию только потому, что она стала жертвой. Выбор сервера решает задачу выхода из конфликта, а не распределения ответственности между модулями. Для исправления важнее воспроизводимая пара последовательностей и объяснение, почему они встретились.
Уменьшите длительность и область конфликта
Изменения выбирают по найденной причине. Если два обработчика берут товары в разном порядке, задайте единое правило для обоих: например, обрабатывать известный набор идентификаторов по возрастанию. Важно распространить договорённость на импорт, административные операции и фоновые задачи; исправление одного маршрута оставляет вторую половину конфликта.
Если блокировки удерживаются во время обращения к доставке, подготовки документа или ожидания пользователя, пересмотрите границу транзакции. Подготовительные вычисления часто можно выполнить заранее, а внешнее действие — оформить отдельным надёжно записанным заданием. При этом проверку актуальности остатка или версии записи нельзя просто вынести наружу и считать действительной навсегда.
Если запрос затрагивает неожиданно широкую область, изучите его условия, план и используемые индексы. Изменение должно сохранять бизнес-ограничения: ускоренный запрос, допускающий продажу отсутствующего товара, проблему не решает. Короткая транзакция также не означает произвольное разделение связанных записей на независимые фиксации.
Единый порядок и меньшая длительность снижают риск конкретных конфликтов, но не дают обещания полного исчезновения deadlock. Остальные пути записи, проверки уникальности и выбранные диапазоны продолжают влиять на конкурентную работу.
Почему увеличение ожидания не устраняет причину
Допустим, в учебной системе обработчик удерживает блокировку 40 секунд из-за зависшего внешнего вызова. У ожидающего запроса предел составляет 10 секунд. Увеличение предела до 60 секунд может позволить ему дождаться ресурса, но пользователь всё равно ждёт десятки секунд. Если HTTP-запрос прекращает ожидание раньше, интерфейс может сообщить об ошибке, пока работа в приложении продолжается.
Согласуйте временные бюджеты всей операции и способ узнать её окончательный результат. Не используйте более длинный таймаут как доказательство исправления. Проверяйте число ожидающих запросов, длительность транзакций и фактическое завершение бизнес-действий.
Принудительное завершение блокирующего соединения тоже требует понимания его роли. Это может быть длительное, но нужное обновление; последующий откат способен занять время. Сначала установите владельца, выполняемую операцию и последствия отмены. Универсальная команда «убить всё старше минуты» опасна для нормальной работы.
Предел ожидания строк не является общим пределом любых блокировок MySQL. Если данные указывают на другой механизм, например ожидание изменения структуры таблицы, диагностику следует продолжить в соответствующей области, а не автоматически менять настройки InnoDB.
Как устроить ограниченный безопасный повтор
Повтор проектируют вокруг одной логической транзакции. После подтверждённого deadlock повторяют её вычисления и записи на актуальном состоянии. После timeout с частичным откатом сначала корректно завершают оставшуюся транзакцию согласно принятой политике. Нельзя запускать новую попытку поверх неизвестного состояния старой или возвращать такое соединение следующему запросу.
- Классифицировать ошибку по данным драйвера, а не по случайному совпадению текста.
- Установить состояние транзакции и выполнить необходимую очистку соединения.
- Проверить, допускает ли бизнес-операция повтор с прежним идентификатором.
- Сделать ограниченную паузу со случайной добавкой вне открытой транзакции.
- Повторить в пределах числа попыток и общего времени, затем вернуть определённый результат или контролируемую ошибку.
Условная политика «не больше трёх попыток, включая первую» означает максимум два повтора. Она не подходит автоматически каждому сайту. При высокой конкуренции немедленные синхронные повторы множества запросов могут снова столкнуться; пауза помогает разнести попытки, а общий бюджет не даёт им продолжаться бесконечно.
Не смешивайте подтверждённый откат с потерей соединения во время фиксации. Во втором случае результат может быть неизвестен: прежде чем создавать новый заказ, требуется установить судьбу прежней операции по устойчивому идентификатору.
Отделите транзакцию от платежа и уведомления
Откат MySQL не отменяет действие, уже принятое внешним сервисом. Если обработчик успел отправить платёжный запрос, письмо или команду отгрузки, повтор всей функции оформления заказа способен повторить этот эффект. Поэтому граница безопасного повторения базы и граница бизнес-процесса должны быть описаны отдельно.
Практическая схема: у операции есть стабильный бизнес-идентификатор, а намерение выполнить внешнее действие записывается согласованно с заказом. Отдельный исполнитель обрабатывает это намерение и сверяет результат. Такой подход требует защиты от повторной обработки и восстановления после остановки между отправкой и сохранением ответа; одного флага «отправлено» недостаточно.
Проверьте поддержку идемпотентности конкретного провайдера и область действия его ключа. Даже если внешний вызов защищён, внутренний резерв или начисление бонусов тоже нуждаются в собственных ограничениях согласованности. Обещание выполнения «ровно один раз» нельзя вывести только из наличия транзакции в одной базе.
В сообщении пользователю различайте отказ операции и ещё проверяемый результат. Просьба повторно нажать кнопку без проверки состояния часто превращает техническую ошибку блокировки в проблему дублей.
Как принять исправление и сохранить наблюдение
Проверку проводят на тестовых данных с воспроизведением конкуренции. Два последовательных успешных клика ничего не говорят о взаимных блокировках. В учебном сценарии запускают параллельные операции с пересекающимся набором товаров и отдельно моделируют timeout на втором шаге многооператорной транзакции.
- После неудачной попытки нет частичного заказа, потерянного резерва или лишнего изменения остатка.
- Повтор использует прежний идентификатор бизнес-операции и ограничен утверждённым бюджетом.
- Соединение после ошибки не оставляет незавершённую транзакцию для следующего запроса.
- Внешние действия не дублируются; неопределённый результат переводится в проверяемое состояние.
- Журналы позволяют связать конфликт, попытки и конечный результат, не раскрывая секреты.
Сравнивайте результаты на сопоставимой нагрузке и наборе операций. Запишите, какой конфликт воспроизведён, каким изменением устранён и какие остальные риски сохраняются. Отсутствие ошибки в одном прогоне полезно, но не заменяет проверку корректности данных и путей обработки исключений.
Счётчики попыток и завершённых заказов храните раздельно. В условном тесте 100 логических операций могут породить 108 попыток: восемь дополнительных обращений ещё не означают восемь потерянных заказов. Проверьте конечный результат каждой из 100 операций, число повторов, время ожидания и отсутствие дублей. Такая сверка показывает пользу исправления для бизнеса, тогда как один счётчик SQL-ошибок описывает только часть картины.
Для внешнего наблюдения за HTTP-доступностью сайта подключите SiteGuard. Сопоставление таких сигналов с внутренними журналами поможет команде увидеть пользовательское проявление сбоя; причины блокировок и корректность операций проверяют средствами приложения и MySQL.