Транзакции, блокировки и deadlock в MySQL

Deadlock в MySQL - это не падение и обычно не баг базы данных. Это InnoDB замечает, что две транзакции ждут блокировку, которую держит другая, выбирает одну для отката и говорит вашему приложению попробовать снова - ошибка 1213, Deadlock found when trying to get lock; try restarting transaction. Сообщение - буквальный совет. Приложения, которые обрабатывают его повтором и держат транзакции короткими, почти не замечают deadlock. Страдают те, у кого длинные транзакции, обновления, сканирующие строки, которые они не меняют, и нет повторов. В этой статье - что на самом деле блокирует InnoDB, чем на практике отличаются уровни изоляции и как найти и устранить ожидания и deadlock, которые у вас возникают.

Транзакции и autocommit#

InnoDB транзакционный, а MySQL по умолчанию работает с autocommit = 1: каждый оператор сам по себе - транзакция, которая фиксируется по его завершении. Чтобы сгруппировать операторы, начните транзакцию явно.

START TRANSACTION;UPDATE accounts SET balance = balance - 25 WHERE id = 1;UPDATE accounts SET balance = balance + 25 WHERE id = 2;COMMIT;   -- or ROLLBACK;

Внутри транзакции блокировки, взятые при записи, держатся до COMMIT или ROLLBACK, а не до завершения оператора. Этот единственный факт объясняет большинство проблем с блокировками: каждая секунда, пока транзакция открыта, - это секунда, в течение которой её блокировки мешают всем остальным.

Что завершает транзакцию без вашей просьбы:

• DDL и некоторые административные операторы - CREATE, ALTER, DROP, TRUNCATE, RENAME, LOCK TABLES и начало другой транзакции - неявно фиксируют её. Изменение схемы в MySQL откатить нельзя, а миграция, которая смешивает DDL и изменения данных в одной «транзакции», не атомарна.
• Отключение откатывает всё незафиксированное.
• Deadlock откатывает всю транзакцию.

SAVEPOINT name и ROLLBACK TO SAVEPOINT name отменяют часть транзакции, не завершая её, - так ORM реализуют вложенные транзакции.

Уровни изоляции и что они значат в InnoDB#

Уровень изоляции определяет, что транзакция видит из изменений других транзакций. В MySQL по умолчанию REPEATABLE READ, в отличие от PostgreSQL и SQL Server, где по умолчанию READ COMMITTED.

Обычные операторы SELECT в InnoDB вообще не берут блокировок. Они читают из согласованного снимка с помощью многоверсионного управления конкурентным доступом (MVCC): старые версии строк хранятся в undo-журнале, поэтому читатель видит данные на момент своего снимка, пока пишущие продолжают работу. При REPEATABLE READ снимок делается при первом чтении в транзакции (или при START TRANSACTION WITH CONSISTENT SNAPSHOT) и сохраняется до конца, поэтому один и тот же запрос каждый раз возвращает одни и те же строки - даже если другие транзакции за это время зафиксировали изменения.

-- Check and change for this sessionSELECT @@transaction_isolation;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;-- Or for the next transaction onlySET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Многие приложения прекрасно работают на READ COMMITTED, а он берёт меньше блокировок промежутков, то есть меньше ожиданий блокировок и меньше deadlock на таблицах с интенсивной вставкой. Это разумное изменение, если делать его осознанно; но это не исправление, которое можно применять вслепую, потому что код, полагавшийся на стабильный снимок внутри транзакции, поведёт себя иначе.

У долгой транзакции есть цена, даже если она только читает: InnoDB не может вычистить старые версии строк, которые ещё могут понадобиться её снимку, поэтому история undo растёт. SHOW ENGINE INNODB STATUS показывает это как History list length; число в миллионах означает, что у чего-то транзакция открыта очень давно.

Блокирующее чтение и потерянное обновление#

Поскольку обычное чтение не блокирует, классическая схема «прочитать - изменить - записать» небезопасна:

-- Two requests run this at the same time for the same accountSELECT balance FROM accounts WHERE id = 1;          -- both read 100UPDATE accounts SET balance = 75 WHERE id = 1;      -- both write 75

Оба запроса увидели 100 и оба записали 75; одно списание исчезло. Три решения, от лучшего к самому общему:

1. Считайте прямо в UPDATE. UPDATE accounts SET balance = balance - 25 WHERE id = 1 AND balance >= 25; атомарен, а rowCount() = 0 говорит, что баланса не хватило.
2. Блокируйте строку при чтении. SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; берёт эксклюзивную блокировку, поэтому второй запрос ждёт, пока первый зафиксируется, и затем читает новое значение.
3. Оптимистическая блокировка. Добавьте колонку version, обновляйте с WHERE id = 1 AND version = 7 и повторяйте, если ни одна строка не подошла. Пока пользователь думает, никаких блокировок не держится.

FOR SHARE (написание LOCK IN SHARE MODE в 8.0) берёт разделяемую блокировку: другие тоже могут заблокировать строку на чтение, но никто не может изменить её, пока вы не зафиксируете транзакцию. Два модификатора делают блокирующее чтение полезным для очередей задач:

-- Fail immediately instead of waitingSELECT * FROM jobs WHERE id = 42 FOR UPDATE NOWAIT;-- Claim the next free job, skipping rows other workers have lockedSTART TRANSACTION;SELECT id, payload FROM jobsWHERE status = 'pending'ORDER BY id LIMIT 1FOR UPDATE SKIP LOCKED;UPDATE jobs SET status = 'running' WHERE id = ?;COMMIT;

SKIP LOCKED позволяет многим воркерам забирать задачи из одной таблицы, не выстраиваясь в очередь друг за другом, - так очередь задач на базе данных остаётся быстрой.

Что на самом деле блокирует InnoDB#

InnoDB блокирует записи индекса, а не строки в абстрактном смысле. Это важнее всего остального в этой статье.

• Блокировка записи (record lock): блокировка одной записи индекса.
• Блокировка промежутка (gap lock): блокировка промежутка между двумя записями индекса, которая не даёт вставлять в него. Используется при REPEATABLE READ, чтобы в заблокированном вами диапазоне не появлялись фантомные строки.
• Next-key блокировка: блокировка записи плюс промежутка перед ней. Именно её REPEATABLE READ использует для блокирующего чтения и для UPDATE и DELETE, которые ищут по диапазону или по неуникальному индексу.
• Блокировка намерения вставки (insert intention lock): особая блокировка промежутка, которую берёт INSERT. Две вставки в один промежуток на разные позиции не блокируют друг друга, но вставка ждёт любую блокировку промежутка, которую держит кто-то другой.

Следствие: блокирующий оператор блокирует каждую запись индекса, которую он проверяет, а не только те, что он меняет. Без подходящего индекса он проверяет - и блокирует - всю таблицу.

-- No index on email: scans and locks every row in usersUPDATE users SET last_seen = NOW() WHERE email = 'a@example.com';-- With an index: locks one record (and a gap under REPEATABLE READ)CREATE INDEX idx_users_email ON users (email);

Первый вариант превращает одновременный вход двух никак не связанных пользователей в ожидание блокировки. Индекс на колонках из условий WHERE в UPDATE, DELETE и SELECT ... FOR UPDATE - это исправление блокировок в той же мере, что и ускорение. Как проверить, что будет сканировать оператор, показано в статье индексы и EXPLAIN в MySQL.

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

Таймауты ожидания блокировки: как найти виновника#

Когда транзакция ждёт блокировку дольше innodb_lock_wait_timeout - по умолчанию 50 секунд, - она получает ошибку 1205, Lock wait timeout exceeded; try restarting transaction.

Ожидание блокировки всегда вызвано другой транзакцией, которая держит блокировку, - обычно той, что открыта слишком долго. Схема sys показывает, кто кого блокирует:

SELECT wait_age, locked_table, locked_index,       waiting_pid, waiting_query,       blocking_pid, blocking_query,       sql_kill_blocking_connectionFROM sys.innodb_lock_waits;

Пустой blocking_query - самый частый и самый показательный результат: блокирующая сессия ничего не выполняет. Она начала транзакцию, изменила строку и затихла - запрос, который упал без отката, но сохранил своё соединение из пула, SQL-клиент разработчика с выключенным autocommit или приложение, делающее HTTP-вызов внутри транзакции. Узнайте, как долго открыты транзакции:

SELECT trx_mysql_thread_id AS pid, trx_state, trx_started,       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS open_s,       trx_rows_locked, trx_rows_modified, trx_queryFROM information_schema.innodb_trxORDER BY trx_started;

KILL <pid> завершает сессию и откатывает её транзакцию. Затем устраните причину - транзакция никогда не должна оставаться открытой, пока код ждёт чего-то, кроме базы данных. performance_schema.data_locks и data_lock_waits показывают каждую отдельную блокировку, если нужны подробности.

Deadlock: откуда берутся и как их читать#

Deadlock - это цикл: транзакция A держит блокировку, которую хочет B, а B держит блокировку, которую хочет A. Ни одна не может продолжить, так что ждать бессмысленно. Детектор deadlock в InnoDB (innodb_deadlock_detect, по умолчанию включён) сразу замечает цикл, откатывает транзакцию, изменившую меньше всего строк, и возвращает ошибку 1213 с SQLSTATE 40001. В отличие от таймаута, транзакция-жертва откатывается целиком.

Типичные схемы:

• Обратный порядок. Один путь в коде обновляет заказ 10, затем 20, другой - 20, затем 10. Решение: всегда блокируйте строки в одном и том же порядке, обычно по первичному ключу, - отсортируйте ID перед циклом.
• Проверить, потом вставить. Две сессии выполняют SELECT ... FOR UPDATE для строки, которой ещё нет (обе получают совместимые блокировки промежутка), а затем обе делают INSERT (каждое намерение вставки ждёт блокировку промежутка другой). Решение: пусть решает уникальный индекс - INSERT ... ON DUPLICATE KEY UPDATE или вставка с перехватом 1062.
• Широкие сканирования. UPDATE с плохо проиндексированным WHERE блокирует гораздо больше строк, чем меняет, и поэтому пересекается со всеми. Решение: индекс.
• Долгие транзакции. Чем дольше транзакция держит блокировки, тем вероятнее, что другой транзакции понадобится одна из них в неправильном порядке.

Последний deadlock полностью описан в выводе SHOW ENGINE INNODB STATUS\G, в разделе LATEST DETECTED DEADLOCK: обе транзакции, оператор, который выполняла каждая, удерживаемые блокировки и ожидаемая блокировка, включая имя индекса. Хранится только последний. Чтобы записывать каждый deadlock в журнал ошибок, включите innodb_print_all_deadlocks - для этого нужен административный аккаунт:

SET PERSIST innodb_print_all_deadlocks = ON;

Читайте строки HOLDS THE LOCK(S) и WAITING FOR THIS LOCK TO BE GRANTED для каждой транзакции; имя индекса там подскажет, на какой путь доступа смотреть. lock_mode X locks gap before rec вместе с insert intention почти всегда означают схему «проверить, потом вставить».

Как правильно повторять#

На нагруженной системе deadlock нельзя исключить, можно лишь сделать редкими. Каждой транзакции, которая может попасть в deadlock, нужен повтор, и повтор должен выполнить всю транзакцию с начала - включая чтения, потому что данные могли измениться.

import timeimport pymysqlRETRYABLE = {1213, 1205}  # deadlock, lock wait timeoutdef run_in_transaction(conn, work, attempts=3):    for attempt in range(1, attempts + 1):        try:            conn.begin()            result = work(conn)            conn.commit()            return result        except pymysql.err.OperationalError as e:            conn.rollback()            if e.args[0] not in RETRYABLE or attempt == attempts:                raise            time.sleep(0.05 * attempt)  # short, growing backoff

Держите повторяемую функцию свободной от побочных эффектов вне базы данных: отправка письма или списание с карты внутри неё означают, что это произойдёт дважды. DB::transaction($callback, 3) в Laravel и шаблоны Django вокруг transaction.atomic() дают ту же структуру; в чистом PDO проверяйте $e->errorInfo[1] на 1213 и 1205, как в статье PHP PDO и MySQL.

Привычки, которые предотвращают большинство проблем с блокировками#

Почти любая проблема с блокировками на небольшом сервере MySQL сводится к нескольким привычкам. Ни одна из них не требует изменения конфигурации:

1. Держите транзакции короткими. Откройте транзакцию, выполните операторы, зафиксируйте. Никогда не вызывайте внутри неё внешний API, не отправляйте письмо, не ждите пользователя и не засыпайте. Сначала медленная работа, потом работа с базой данных.
2. Индексируйте то, что блокируете. Каждый UPDATE, DELETE и SELECT ... FOR UPDATE должен находить свои строки через индекс, чтобы блокировать нужные строки, а не таблицу.
3. Блокируйте в одном и том же порядке. Когда транзакция затрагивает несколько строк, сначала отсортируйте их по первичному ключу. Две транзакции, которые всегда блокируют в одном порядке, не могут попасть в deadlock на этих строках.
4. Предпочитайте атомарные операторы. UPDATE ... SET stock = stock - 1 WHERE stock > 0 лучше, чем прочитать, проверить, записать.
5. Пусть арбитром будут уникальные индексы. Вставляйте и обрабатывайте ошибку 1062 вместо предварительной проверки.
6. Разбивайте большие изменения на пакеты. Удаление двух миллионов старых строк одним оператором держит два миллиона блокировок и раздувает undo-журнал; удаление по 5000 за раз в цикле, каждое в своей транзакции, пропускает другую работу между пакетами.
7. Повторяйте 1213 и 1205 всей транзакцией, несколько раз, с короткой паузой.

Пакетная обработка заслуживает примера, потому что именно её пропускают:

-- Repeat until it affects 0 rowsDELETE FROM events WHERE created_at < '2026-01-01' ORDER BY id LIMIT 5000;

Блокировки метаданных: когда ALTER TABLE вешает всё#

Над блокировками строк InnoDB есть вторая система блокировок. Любой оператор, затрагивающий таблицу, берёт на неё блокировку метаданных и держит её до конца транзакции. ALTER TABLE нужна эксклюзивная блокировка метаданных, поэтому он ждёт каждую открытую транзакцию, которая касалась таблицы, - а каждый новый запрос к таблице встаёт в очередь за ожидающим ALTER. Одна простаивающая транзакция плюс одна миграция могут остановить весь сайт, и каждый запрос будет показывать Waiting for table metadata lock.

lock_wait_timeout, который управляет блокировками метаданных, по умолчанию равен 31 536 000 секундам - году. Прежде чем выполнять DDL на живой системе, поставьте его низким для своей сессии, чтобы миграция сдалась, а не заморозила трафик:

SET SESSION lock_wait_timeout = 5;ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;

Если не получилось, найдите простаивающую транзакцию запросом к innodb_trx выше (или через sys.schema_table_lock_waits), разберитесь с ней и попробуйте снова. Остальная часть этого процесса описана в статье миграции без простоя.

FAQ#

Deadlock - признак того, что что-то сломано?

Редкие deadlock нормальны для любой системы с конкурентной записью, и повтор с ними справляется. Постоянный поток или deadlock между одними и теми же двумя операторами каждые несколько минут указывают на проблему с порядком доступа или индексами, которую стоит исправить. Включите innodb_print_all_deadlocks и ищите закономерность.

Стоит ли перейти на READ COMMITTED?

Если у вас нагрузка с интенсивной вставкой и вы видите deadlock на блокировках промежутков, это часто помогает, и многие фреймворки на нём протестированы. Меняйте уровень для сессии или глобально, затем протестируйте пути в коде, которые читают одни и те же данные дважды в одной транзакции. Не ждите, что это исправит ожидания блокировок из-за долгих транзакций: их не исправляет ничего, кроме более коротких транзакций.

Стоит ли увеличить innodb_lock_wait_timeout?

Редко. Пятьдесят секунд - уже больше, чем должен ждать любой веб-запрос. Понижайте его для интерактивной нагрузки (5-10 секунд), чтобы застрявшие запросы падали быстро, и повышайте только для пакетных задач, которые сознательно ждут за другими пакетными задачами.

Блокирует ли SELECT оператор UPDATE в MySQL?

Обычный SELECT - нет: он читает снимок и не берёт блокировок строк. SELECT ... FOR UPDATE и FOR SHARE берут, а каждый SELECT берёт разделяемую блокировку метаданных на таблицу, которая блокирует ALTER TABLE, но не UPDATE.

Можно ли выключить обнаружение deadlock?

Да, через innodb_deadlock_detect = OFF, после чего deadlock разрешаются по innodb_lock_wait_timeout. Это существует для систем с очень высокой конкурентностью, где само обнаружение становится узким местом. На небольшом сервере оставьте его включённым: мгновенное обнаружение лучше 50-секундного ожидания.