Тайм-аут ожидания блокировки транзакции MySQL Превышен тайм-аут ожидания блокировки; попробуйте перезапустить транзакцию

MySQL

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

сценарий проблемы

Сценарий возникновения проблемы:

  1. Когда очередь сообщений обрабатывает сообщения, операции вставки и обновления выполняются с одними и теми же данными в одной и той же транзакции;
  2. Несколько серверов работают с одной и той же базой данных;
  3. Мгновенный феномен высокого параллелизма;

Данные часто автоматически откатываются после обновления или добавления данных, общий отчет операций с таблицейLock wait timeout exceededи долгое время нет ответа

Анализ проблемы

Анализ причин

MySql Lock wait timeout exceededЯ полагаю, что все знакомы с этой проблемой, но многие люди не очень понимают ее причину и способы ее решения. Во многих случаях поиск и устранение проблемы остается на усмотрение администратора баз данных. Далее мы сосредоточимся на этой проблеме.

Существует множество ситуаций, в которых Mysql вызывает блокировки, вот некоторые из них:

  1. Если операция DML выполняется без фиксации, операция удаления заблокирует таблицу.
  2. Вставляйте и обновляйте одни и те же данные в одной и той же транзакции.
  3. Неправильно спроектированные индексы таблиц приводят к взаимоблокировкам в базе данных.
  4. Длинные транзакции, блокировка DDL, а затем блокировка всех последующих операций над той же таблицей.

Но разница в томLock wait timeout exceededиDead Lockэто не то же самое.

  • Lock wait timeout exceeded: транзакция после подтверждения ожидает, пока ранее обработанная транзакция освободит блокировку, но когда ожидание превысит время ожидания блокировки mysql, будет возбуждено это исключение.
  • Dead Lock: две транзакции ждут друг друга, чтобы снять блокировку одного и того же ресурса, что приводит к бесконечному циклу, который вызовет это исключение.

Еще одна вещь, которую следует отметить, это то, чтоinnodb_lock_wait_timeoutиlock_wait_timeoutТоже разные.

  • innodb_lock_wait_timeout: время ожидания для блокировок на уровне строк операций innodb dml.
  • lock_wait_timeout: Время ожидания операции блокировки структуры данных ddl.

Итак, как проверить конкретное значение innodb_lock_wait_timeout:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'

Как изменить значение тайм-аута ожидания блокировки внутреннего узла, объем изменения параметра включает сеанс и глобальный, и поддерживает динамическое изменение, есть два способа изменения:

метод первый:

Измените следующим утверждением

set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;

ps Обратите внимание, что модификация global не влияет на текущий поток и вступает в силу только при установлении нового соединения.

Способ второй:

Изменить файл параметров/etc/my.cnf innodb_lock_wait_timeout = 50

ps. innodb_lock_wait_timeoutОтносится к максимальному времени ожидания транзакции для получения ресурсов.Если ресурс не будет выделен по истечении этого времени, он вернет сбой приложения, когда ожидание блокировки превысит установленное время, будет сообщено о следующей ошибке;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Единицей времени его параметров являются секунды, минимум может быть установлен на 1 с (как правило, не так уж и мало), максимум может быть установлен на 1073741824 секунды, значение по умолчанию 50 с во время установки (настройка параметра по умолчанию).

Сводка причин

  • В случае высокого параллелизма транзакции Spring вызывают взаимоблокировку базы данных, а последующие операции завершаются тайм-аутом и вызывают исключения.
  • База данных Mysql принимает режим InnoDB. Параметр по умолчанию: innodb_lock_wait_timeout устанавливает время ожидания блокировки на 50 с. Как только блокировка базы данных превысит это время, будет сообщено об ошибке.

Решение

  • Экстренный метод:show full processlist; killОтбросьте рассматриваемый процесс.ps.Иногда через processlist невозможно увидеть, где находится ожидающая блокировка.Когда обе транзакции находятся в стадии фиксации, это не может быть отражено в processlist.
  • Радикальный метод:select * from information_schema.innodb_trx;Посмотрите, какие транзакции занимают ресурсы таблицы.ps. С помощью этого метода вам нужно иметь некоторое представление о innodb, чтобы иметь дело с
  • Увеличьте время ожидания блокировки, то есть увеличьте значение параметра следующего элемента конфигурации, единица измерения – секунда (с)innodb_lock_wait_timeout=500
  • Оптимизируйте хранимые процедуры и транзакции, чтобы избежать длительного ожидания

Звучит просто, найти и убить его, но на самом деле это не так просто, как кажется. Когда возникает проблема, нам нужно проанализировать причину проблемы и найти бизнес-код через причину. Могут быть проблемы в некоторых местах, чтобы избежать проблем в будущем Столкнулся с той же проблемой.

Расширенная информация

Справочная информация

  1. Время ожидания блокировки истекло. Это вызвано тем, что текущая транзакция ожидает освобождения ресурсов блокировки другими транзакциями. Вы можете найти таблицы и операторы, конкурирующие за ресурсы блокировки, оптимизировать SQL, создать индексы и т. д. Если это все еще не работает, вы можете соответствующим образом уменьшить количество одновременных потоков.
  2. Истечение времени ожидания транзакции во время ожидания блокировки таблицы Предполагается, что таблица заблокирована другим процессом и не была освобождена. Вы можете использовать SHOW INNODB STATUS/G;, чтобы увидеть ситуацию с блокировкой.
  3. Найдите решение и добавьте: TransactionDeadLockDetectionTimeOut=10000 (установлено на 10 секунд) по умолчанию 1200 (1,2 секунды)
  4. InnoDB автоматически обнаружит взаимоблокировку и откатится назад или завершит ситуацию взаимоблокировки.

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

Если параметр innodb_table_locks=1 и autocommit=0, InnoDB будет обращать внимание на взаимоблокировки таблиц и блокировки на уровне строк на уровне MySQL. Кроме того, InnoDB не обнаруживает взаимоблокировки с помощью команды MySQL Lock Tables и других механизмов хранения. Вы должны установить innodb_lock_wait_timeout, чтобы исправить эту ситуацию. innodb_lock_wait_timeout — это тайм-аут для Innodb, чтобы отказаться от блокировок на уровне строк.

Объяснение таблиц Innodb_*

MysqlизInnoDBМеханизм хранения поддерживает транзакции, и транзакция не активируется после ее открытия.Commit. В результате ресурс занят надолго.При вытеснении ресурса другими транзакциями вытеснение не выполняется из-за блокировки предыдущей транзакции! таким образом появляютсяLock wait timeout exceeded

Следующие таблицы представляют собой таблицы информации о транзакциях и блокировках innodb.Понимание этих таблиц может помочь вам обнаружить проблему.

  • innodb_trx## Все текущие транзакции
  • innodb_locks## Текущая блокировка
  • innodb_lock_waits## Соответствие ожидания блокировки

следующая параinnodb_trxКаждое поле таблицы поясняется:

trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

следующая параinnodb_locksКаждое поле таблицы поясняется:

lock_id：锁 ID。
lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：被锁定的或者包含锁定记录的表的名称。
lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

следующая параinnodb_lock_waitsКаждое поле таблицы поясняется:

requesting_trx_id：请求事务的 ID。
requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事务的 ID。
blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

Шаги обработки ожидания блокировки

  • Просмотр таблицы innodb_lock_waits напрямую
SELECT * FROM innodb_lock_waits;
  • Таблица innodb_locks объединяется с таблицей innodb_lock_waits:
SELECT * FROM innodb_locks WHERE lock_trx_id IN (SELECT blocking_trx_id FROM innodb_lock_waits);
  • Таблица innodb_locks ПРИСОЕДИНЯЙТЕСЬ к таблице innodb_lock_waits:
SELECT innodb_locks.* FROM innodb_locks JOIN innodb_lock_waits ON (innodb_locks.lock_trx_id = innodb_lock_waits.blocking_trx_id);
  • Запросите таблицу innodb_trx:
SELECT trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query FROM innodb_trx WHERE trx_state = 'LOCK WAIT';
  • trx_mysql_thread_id убивает идентификатор потока транзакции
SHOW ENGINE INNODB STATUS ;
SHOW PROCESSLIST ;

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

kill ID;
Категории