Фиксация "Тайм-аут блокировки ожидания превышен, попробуйте перезапустить транзакцию" для "застрявшей" таблицы Mysql?

Из script я отправил такой запрос тысячу раз в мою локальную базу данных:

update some_table set some_column = some_value

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

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

Lock wait timeout exceeded; try restarting transaction

Это таблица innodb, поэтому застрявшая транзакция, вероятно, неявная. Как я могу исправить эту таблицу и удалить из нее застрявшую транзакцию?

Ответ 1

Я решил проблему, отбросив таблицу и восстановив ее из резервной копии.

Ответ 2

У меня была аналогичная проблема, и я решил ее, проверив потоки, которые запущены. Чтобы увидеть текущие потоки, используйте следующую команду в интерфейсе командной строки mysql:

SHOW PROCESSLIST;

Он также может быть отправлен из phpMyAdmin, если у вас нет доступа к интерфейсу командной строки mysql.
Это отобразит список потоков с соответствующими идентификаторами и временем выполнения, поэтому вы можете УБИТЬ потоки, которые занимают слишком много времени для выполнения. В phpMyAdmin у вас будет кнопка для остановки потоков с помощью KILL, если вы используете интерфейс командной строки, просто используйте команду KILL, за которой следует идентификатор потока, как в следующем примере:

KILL 115;

Это приведет к завершению соединения для соответствующего потока.

Ответ 3

Вы можете проверить текущие транзакции с помощью

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`

Ваша транзакция должна быть одной из первых, потому что она самая старая в списке. Теперь просто возьмите значение trx_mysql_thread_id и отправьте ему команду KILL:

KILL 1234;

Если вы не знаете, какая транзакция принадлежит вам, очень часто повторяйте первый запрос и посмотрите, какие транзакции сохраняются.

Ответ 4

Это началось со мной, когда размер моей базы данных вырос, и я делал много транзакций на нем.

Правда, есть, вероятно, какой-то способ оптимизировать ваши запросы или вашу БД, но попробуйте эти 2 запроса для исправления работы.

Запустите это:

SET GLOBAL innodb_lock_wait_timeout = 5000;

И тогда это:

SET innodb_lock_wait_timeout = 5000;

Ответ 5

Проверить статус InnoDB для блокировок

SHOW ENGINE InnoDB STATUS;

Проверить открытые таблицы MySQL

SHOW OPEN TABLES WHERE In_use > 0;

Проверить ожидающие транзакции InnoDB

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

Проверить зависимость блокировки - что блокирует что

SELECT * FROM `information_schema`.`innodb_locks`;

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

Коренной причиной проблемы может быть и ваш код. Пожалуйста, проверьте связанные функции, особенно для аннотаций, если вы используете JPA, например, Hibernate.

Например, как описано здесь, неправильное использование следующей аннотации может привести к блокировкам в базе данных:

@Transactional(propagation = Propagation.REQUIRES_NEW)

Ответ 6

Перезагрузите MySQL, он отлично работает.

НО НО остерегайтесь, что если такой запрос застрял, возникает проблема где-то:

в вашем запросе (неуместен char, декартовый продукт,...)
очень много записей для редактирования
сложные комбинации или тесты (MD5, подстроки, LIKE %...% и т.д.)
проблема структуры данных
модель внешнего ключа (блокировка цепочки/петли)
неверно проиндексированные данные

Как сказал @syedrakib, это работает, но это не долгоживущее решение для производства.

Остерегайтесь: выполнение перезапуска может повлиять на ваши данные с несогласованным состоянием.

Также вы можете проверить, как MySQL обрабатывает ваш запрос с помощью ключевого слова EXPLAIN и видит, возможно ли что-то ускорить запрос (индексы, сложные тесты,...).

Ответ 7

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

Решение: закройте соединение или setAutoCommit(true) (в соответствии с вашим дизайном), чтобы снять блокировку.

Ответ 8

Перейти к процессам в mysql.

Итак, можно видеть, что еще есть работа.

Убейте конкретный процесс или дождитесь завершения процесса.

Ответ 9

У меня возникла эта проблема при попытке удалить определенную группу записей (используя MS Access 2007 с подключением ODBC к MySQL на веб-сервере). Обычно я удаляю некоторые записи из MySQL, а затем заменяю обновленные записи (каскад удаляет несколько связанных записей, это упрощает удаление всех связанных записей для удаления одной записи).

Я попытался выполнить операции, доступные в phpMyAdmin для таблицы (оптимизация, флеш и т.д.), но я получил разрешение на ошибку RELOAD при попытке сбросить. Поскольку моя база данных находится на веб-сервере, я не мог перезапустить базу данных. Восстановление из резервной копии не было вариантом.

Я попытался запустить запрос удаления для этой группы записей в доступе cPanel mySQL в Интернете. Получено такое же сообщение об ошибке.

Мое решение: я использовал бесплатный браузер MySQL Query от Sun (Oracle), который ранее был установлен на моем компьютере, и запускал там запрос на удаление. Это сработало сразу, проблема решена. Затем я смог снова выполнить функцию, используя Access script, используя соединение ODBC Access to MySQL.

Ответ 10

Я столкнулся с той же проблемой с "обновлением" -состояния. Моим решением было просто выполнить операции, доступные в phpMyAdmin для таблицы. Я оптимизировал, очищал и дефрагментировал таблицу (не в том порядке). Не нужно бросать стол и восстанавливать его из резервной копии для меня.:)

Ответ 11

У меня была такая же проблема. Я думаю, что это проблема с SQL. Вы можете просто закрыть SQL-процесс из диспетчера задач. Если это не исправить, просто перезагрузите компьютер. Вам не нужно бросать таблицу и перезагружать данные.

Ответ 12

Исправлено.

Убедитесь, что в запросе не вставлен несоответствующий тип данных. У меня была проблема, когда я пытался "данные агента браузера пользователя" в VARCHAR(255) и имел проблему с этой блокировкой, однако, когда я изменил ее на TEXT(255), она исправила ее.

Так что, скорее всего, это несоответствие типа данных.