MySQL ошибки 1205 и 1213: как диагностировать lock wait timeout и deadlock в InnoDB

Что означают ошибки MySQL 1205 и 1213 — и почему их нельзя «лечить таймаутами»

Когда база начинает «захлёбываться» от параллельных изменений, чаще всего всплывают два сообщения:

• MySQL 1205: Lock wait timeout exceeded; try restarting transaction — транзакция слишком долго ждала блокировку и завершилась ошибкой по таймауту.
• MySQL 1213: Deadlock found when trying to get lock; try restarting transaction — InnoDB обнаружил взаимную блокировку (deadlock), выбрал «жертву» и откатил одну транзакцию, чтобы разблокировать остальные.

Ключевая разница: при 1213 система сама распознала тупик и быстро «разрубила» его откатом; при 1205 тупика может не быть — просто кто-то удерживает нужный ресурс слишком долго, и ожидание дошло до innodb_lock_wait_timeout.

Опасный анти‑паттерн — лечить проблему только увеличением innodb_lock_wait_timeout. Это может спрятать симптомы, но ухудшить время отклика, накопить «висящие» транзакции и увеличить очередь ожиданий. Правильный подход: понять, кто кого блокирует, почему блокирует и как сделать критическую секцию короче.

Быстрый чек-лист: что посмотреть в первую минуту инцидента

Если пошли 1205/1213 в логах приложения или мониторинге, действуйте по шагам:

1. Проверьте, не копятся ли долгие транзакции (часто виноваты «забытые» транзакции в приложении).
2. Снимите SHOW ENGINE INNODB STATUS и найдите секцию про последний deadlock.
3. Посмотрите активные ожидания блокировок и кто блокирует кого через performance_schema (или хотя бы через information_schema, если P_S недоступна).
4. Проверьте индексы и порядок захвата блокировок в коде (одинаковый порядок операций снижает риск deadlock).

Проверить «долго живущие» транзакции

Частый источник проблем — транзакции, которые открыты секунды/минуты: они держат блокировки, раздувают undo из‑за MVCC и усиливают конкуренцию. Быстрый просмотр:

mysql -e "SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY age_s DESC\G"

Если сверху списка транзакции с большим age_s, обычно это и есть «пробка». Дальше нужно связать trx_mysql_thread_id с конкретной сессией и запросом.

Посмотреть, кто блокирует кого (минимально)

Если в вашей версии есть таблицы блокировок в information_schema (зависит от версии MySQL/MariaDB), можно попробовать:

mysql -e "SELECT * FROM information_schema.innodb_lock_waits\G"

На современных MySQL надёжнее опираться на performance_schema: там больше деталей и меньше сюрпризов по доступности данных.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Диагностика через InnoDB status: где искать deadlock и долгие ожидания

Команда, которую стоит знать наизусть:

mysql -e "SHOW ENGINE INNODB STATUS\G"

В выводе ищите два важных фрагмента:

• LATEST DETECTED DEADLOCK — подробности последнего deadlock (максимально полезно именно для 1213).
• Секции про транзакции и ожидания — кто держит блокировки, что именно ждут другие, какие записи/индексы участвуют.

Важно: SHOW ENGINE INNODB STATUS показывает «последний» обнаруженный deadlock. Если дедлоков много, вы будете видеть только самый свежий. В пике полезно снять несколько дампов с интервалом и сохранить их в тикет.

Как читать LATEST DETECTED DEADLOCK (практически)

Обычно там есть:

• несколько транзакций с SQL (иногда обрезанным);
• какие индексы/записи они блокируют;
• какая транзакция стала «жертвой» (victim) и была откатана.

Практический смысл: вы находите пару запросов, которые берут блокировки в разном порядке. Дальше цель — сделать порядок одинаковым, сократить время удержания блокировки или изменить схему/индексы так, чтобы зона блокировок была меньше.

Диагностика через performance_schema: быстрые запросы для админа

Если включён performance_schema (в большинстве установок MySQL 8 он включён), вы можете увидеть ожидания блокировок точнее и связать их с конкретными потоками.

Найти текущие ожидания блокировок

mysql -e "SELECT * FROM performance_schema.data_lock_waits\G"

Таблица показывает связку «кто ждёт» и «кто блокирует» на уровне конкретных lock’ов.

Посмотреть сами блокировки

mysql -e "SELECT ENGINE, ENGINE_LOCK_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, THREAD_ID FROM performance_schema.data_locks ORDER BY OBJECT_SCHEMA, OBJECT_NAME\G"

Отсюда обычно идёте к THREAD_ID, чтобы понять, что именно выполняется и кем.

Связать THREAD_ID с сессией и запросом

mysql -e "SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST, t.PROCESSLIST_DB, t.PROCESSLIST_TIME, t.PROCESSLIST_STATE, LEFT(t.PROCESSLIST_INFO, 200) AS info_200 FROM performance_schema.threads t WHERE t.PROCESSLIST_ID IS NOT NULL ORDER BY t.PROCESSLIST_TIME DESC\G"

Так вы обычно находите запрос, который удерживает блокировку (часто это UPDATE/DELETE/INSERT…SELECT или SELECT … FOR UPDATE), и запрос, который ждёт.

Если блокировки и ожидания повторяются «волнами», полезно параллельно проверить базовые настройки и I/O-профиль InnoDB: иногда проблема начинается с замедления диска/буфер-пула, а уже затем выстреливает конкуренция транзакций. По теме — статья про тюнинг InnoDB buffer pool и I/O.

Типовые причины lock wait timeout (1205) в реальных проектах

1) Долгая транзакция держит блокировки

Сценарий: приложение начинает транзакцию, делает UPDATE, затем уходит в сеть (вызов API), выполняет тяжёлый SELECT, ждёт очередь воркера — и только потом делает COMMIT. Всё это время строки и/или диапазоны индекса могут быть заблокированы.

Лечение: выносить внешние действия за пределы транзакции, сокращать транзакции до «изменил данные → commit», избегать интерактивных транзакций.

2) Нет нужного индекса — InnoDB блокирует больше строк, чем вы думаете

Когда UPDATE/DELETE не использует селективный индекс, движок вынужден просканировать много записей и держать блокировки существенно шире. Под нагрузкой это быстро превращается в 1205.

Проверка: EXPLAIN для проблемного UPDATE/DELETE, анализ rows и выбранного индекса. Исправление: добавить/скорректировать индекс под условие WHERE и порядок соединений.

3) Ожидание метаданных (MDL) маскируется под «всё зависло»

Отдельный класс блокировок — metadata locks (MDL). Например, DDL ждёт завершения долгого SELECT, а новые запросы начинают ждать DDL. На уровне приложения это выглядит как лавина таймаутов и «залипание» базы, хотя первопричина — один долгий запрос/транзакция.

4) Горячие строки (hot rows): счётчики, балансы, очереди

Классика: глобальный счётчик, таблица с единственной строкой «настройки», «последний номер заказа», один и тот же баланс. Десятки параллельных транзакций бьются за одну запись — 1205 становится регулярным.

Варианты решения: шардирование счётчика (несколько строк), перенос части логики в append-only таблицы, уменьшение частоты обновлений, оптимизация бизнес‑процесса и параллелизма.

Типовые причины deadlock (1213) и как их устранять

1) Разный порядок обновления строк в разных частях кода

Самая частая причина 1213: два запроса обновляют одни и те же сущности, но в разном порядке. Например, один поток сначала обновляет таблицу A, потом B; другой — сначала B, потом A. В параллели получается цикл ожиданий.

Лечение: стандартизировать порядок захвата блокировок. Если всегда обновлять таблицы и строки в одном порядке (например, по первичному ключу), вероятность deadlock заметно падает.

2) Диапазонные блокировки и next-key locks

InnoDB при определённых условиях (особенно при уровне изоляции REPEATABLE READ) использует next-key locks, чтобы защититься от «фантомов». Это может давать неожиданные блокировки диапазонов индекса и дедлоки при конкурентных вставках/обновлениях «рядом» по ключу.

Что делать на практике:

• проверить, есть ли подходящий индекс, чтобы запросы били по точному диапазону, а не по размазанному условию;
• пересмотреть условия WHERE и типы сравнения (чтобы индекс реально использовался);
• точечно оценить перевод части транзакций на READ COMMITTED (только после понимания последствий для согласованности).

3) Пакетные UPDATE/DELETE на тысячи строк

Большие батчи держат блокировки долго и пересекаются с «онлайновой» нагрузкой. Даже если это не приводит к 1205, это легко приводит к 1213 при пересечении наборов строк с другими транзакциями.

Решение: дробить на батчи по первичному ключу, коммитить чаще, выбирать стабильный порядок обработки.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Практика: как безопасно «разрулить» ситуацию в моменте

1) Найти блокирующую сессию и оценить последствия

Когда вы видите «кто блокирует», не спешите убивать соединение. Сначала поймите, что это за транзакция: миграция, важная бизнес‑операция, фоновая джоба. Иногда корректнее временно снизить параллелизм воркеров, чем рубить одну критичную транзакцию.

2) Точечно завершить проблемный запрос/сессию

Если решение очевидно (например, зависшая транзакция держит блокировки уже минуты), можно остановить конкретную сессию:

mysql -e "SHOW PROCESSLIST"

mysql -e "KILL 12345"

Иногда уместнее отменить только текущий запрос, оставив соединение:

mysql -e "KILL QUERY 12345"

Разница: KILL QUERY пытается отменить выполняющийся запрос; KILL завершает соединение целиком, что откатывает активную транзакцию.

3) Правильный retry транзакции на уровне приложения

И 1205, и 1213 по смыслу предполагают повтор (try restarting transaction), но повтор должен быть безопасным:

• только для идемпотентных операций или с защитой от дублей (уникальные ключи, токены идемпотентности);
• с экспоненциальной задержкой и джиттером, чтобы не усиливать шторм;
• с ограничением числа попыток и понятной деградацией (очередь, ретраи на воркере, ответ пользователю).

Системные исправления: как снизить вероятность 1205/1213

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

Практическая цель — чтобы транзакция выполнялась быстро и предсказуемо:

• не держите транзакцию открытой во время сетевых вызовов и долгих вычислений;
• делайте меньше SQL внутри одной транзакции;
• обновляйте только нужные строки и столбцы.

Добейтесь правильных индексов под UPDATE/DELETE

Для конкурентных систем важны не только индексы под SELECT, но и индексы под модификации. Если условие WHERE не использует индекс, вероятность конфликтов под нагрузкой резко растёт.

• постройте EXPLAIN для проблемных запросов;
• добавьте составные индексы под фактические условия фильтрации;
• проверьте, что типы столбцов и сравнения не ломают использование индекса (например, функции над полем в WHERE).

Стандартизируйте порядок операций (против deadlock)

Если бизнес‑операция обновляет несколько таблиц/строк — определите жёсткий порядок. Пример: всегда блокируем «родительскую» сущность, потом «дочернюю», а множества id всегда сортируем по возрастанию и обрабатываем в этом порядке.

Дробите батчи и используйте «окна» по PK

Вместо «одним махом удалить всё старое» лучше делать порционно: батч по первичному ключу, лимит строк на транзакцию, короткая пауза между батчами при высокой нагрузке. Так вы уменьшаете и время удержания блокировок, и площадь конфликтов.

Настройки: что можно подкрутить, а что лучше не трогать без причины

Настройки не заменяют исправления логики, но помогают стабилизировать систему:

• innodb_lock_wait_timeout — увеличение может снизить количество 1205, но увеличит время ожидания и ухудшит деградацию под нагрузкой.
• Уровень изоляции — иногда READ COMMITTED снижает влияние next-key locks, но требует понимания эффектов на согласованность и повторяемость чтений.

Если вы не можете объяснить, зачем меняете параметр и какой риск принимаете — сначала найдите конкретный конфликтующий запрос и исправьте его.

Как собрать доказательства для разработчиков: что приложить к тикету

Чтобы исправление не превратилось в «покрутили таймауты», собирайте одинаковый набор артефактов:

• дамп SHOW ENGINE INNODB STATUS\G в момент проблемы (несколько раз с интервалом);
• вывод из performance_schema.data_lock_waits и performance_schema.data_locks;
• идентификаторы потоков/сессий и обрезанный текст запросов;
• EXPLAIN для запросов, которые держат и/или ждут блокировки;
• контекст: RPS, количество воркеров, тип нагрузки (пакетная задача, импорт, миграция).

Если вы параллельно включаете изменения в топологии (репликация, переключения, semisync), фиксируйте это в таймлайне инцидента: конкуренция блокировок иногда «всплывает» именно при деградации реплики/фейловере. См. также практику по GTID и semisync при failover.

Итоги: как мыслить про 1205/1213 правильно

Lock wait timeout и deadlock — это не «ошибка MySQL», а сигнал, что конкуренция транзакций вышла за пределы вашей текущей модели данных, индексов и паттернов доступа. Хорошая новость: почти всегда есть конкретная пара запросов и конкретная причина.

• Сначала диагностика: InnoDB status, ожидания блокировок, долгие транзакции.
• Потом исправления: запросы, индексы, порядок блокировок, дробление батчей.
• И только затем тонкая настройка таймаутов и изоляции, если это действительно нужно.

Если вы регулярно видите 1205 и 1213 под нагрузкой — пересмотрите критические транзакции: сделайте их короткими, предсказуемыми и хорошо индексированными. Это почти всегда даёт больший эффект, чем «магические» параметры.