Получено сообщение "Превышено время ожидания блокировки; попробуйте перезапустить транзакцию", хотя я не использую транзакцию
Я использую следующий MySQL UPDATE заявление:
mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Я не использую транзакцию, так почему я получаю эту ошибку? Я даже попытался перезапустить свой сервер MySQL, и это не помогло.
В таблице 406 733 строки.
27 ответов
Вы используете транзакцию; autocommit не отключает транзакции, он просто автоматически фиксирует их в конце оператора.
Происходит следующее: какой-то другой поток слишком долго удерживает блокировку записи для какой-либо записи (вы обновляете каждую запись в таблице!), И время вашего потока истекло.
Вы можете увидеть более подробную информацию о событии, выпустив
SHOW ENGINE INNODB STATUS
после события (в sql редактор). Идеально делать это на тихой тест-машине.
Как принудительно разблокировать заблокированные таблицы в MySQL:
Взлом таких блокировок может привести к тому, что атомарность в базе данных не будет применена к операторам sql, вызвавшим блокировку.
Это хакерское решение, и правильное решение - исправить приложение, которое вызвало блокировки. Однако, когда на кону доллары, быстрый удар заставит вещи снова двигаться.
1) Войдите в MySQL
mysql -u your_user -p
2) Давайте посмотрим список заблокированных таблиц
mysql> show open tables where in_use>0;
3) Давайте посмотрим список текущих процессов, один из них блокирует ваши таблицы
mysql> show processlist;
4) Убей один из этих процессов
mysql> kill <put_process_id_here>;
mysql> set innodb_lock_wait_timeout=100
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100 |
+--------------------------+-------+
Теперь снова активируйте замок. У вас есть 100 секунд, чтобы выдать SHOW ENGINE INNODB STATUS\G в базу данных и посмотреть, какая другая транзакция блокирует вашу.
Посмотрите, хорошо ли настроена ваша база данных. Особенно изоляция транзакций. Не стоит увеличивать переменную innodb_lock_wait_timeout.
Проверьте уровень изоляции транзакции вашей базы данных в mysql cli:
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)
Вы можете получить улучшения, изменяя уровень изоляции, используя оракул, такой как READ COMMITTED, вместо REPEATABLE READ (InnoDB Defaults)
mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql>
Также попробуйте использовать SELECT FOR UPDATE только в случае необходимости.
Ни одно из предложенных решений не сработало для меня, но это сработало.
Что-то блокирует выполнение запроса. Скорее всего, другой запрос обновляется, вставляется или удаляется из одной из таблиц вашего запроса. Вы должны выяснить, что это такое:
SHOW PROCESSLIST;
Найдя процесс блокировки, найдите его id и запустить:
KILL {id};
Повторите ваш начальный запрос.
mysql->SHOW PROCESSLIST;
kill xxxx;
а затем убить кого во сне. В моем случае это 2456.
100% с тем, что сказал MarkR. autocommit делает каждый оператор транзакцией одного оператора.
SHOW ENGINE INNODB STATUS должен дать вам некоторые подсказки относительно причины тупика. Внимательно посмотрите на свой медленный журнал запросов, чтобы увидеть, что еще запрашивает таблицу, и попробуйте удалить все, что выполняет полное сканирование таблицы. Блокировка на уровне строк работает хорошо, но не тогда, когда вы пытаетесь заблокировать все строки!
Попробуйте обновить следующие два параметра, так как они должны иметь значения по умолчанию.
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = ВКЛ
Для проверки значения параметра вы можете использовать приведенный ниже SQL.
ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ, КАК 'innodb_rollback_on_timeout';
В нашем случае проблема была не в самих замках.
Проблема заключалась в том, что одной из конечных точек нашего приложения необходимо было открыть 2 соединения параллельно для обработки одного запроса.
Пример:
- Откройте 1-е соединение
- Начать транзакцию 1
- Заблокировать 1 строку в таблице 1
- Откройте 2-е соединение
- Начать транзакцию 2
- Заблокировать 1 строку в таблице 2
- Подтвердить транзакцию 2
- Освободите 2-е соединение
- Подтвердить транзакцию 1
- Освободить 1-е соединение
Наше приложение имело пул соединений, ограниченный 10 соединениями.
К сожалению, под нагрузкой, как только все соединения были использованы, приложение перестало работать, и у нас началась эта проблема. У нас было несколько запросов, которым нужно было открыть второе соединение для завершения, но мы не могли этого сделать из-за ограничения пула соединений. Как следствие, эти запросы сохраняли блокировку строки table1 в течение длительного времени, что приводило к следующим запросам, которым нужно было заблокировать ту же строку, чтобы вызвать эту ошибку.
Решение:
- В краткосрочной перспективе мы исправили проблему, увеличив лимит пула соединений.
- В долгосрочной перспективе мы удалили все вложенные соединения, чтобы полностью решить проблему.
Советы:
Вы можете легко проверить, есть ли у вас вложенные соединения, попробовав снизить лимит пула соединений до 1 и протестировать свое приложение.
Можете ли вы обновить любую другую запись в этой таблице, или эта таблица интенсивно используется? Я думаю о том, что пока он пытается получить блокировку, ему нужно обновить эту запись, тайм-аут установленного времени истек. Вы можете увеличить время, которое может помочь.
Если вы только что убили большой запрос, потребуется время, чтобы rollback, Если вы выполните другой запрос до того, как завершенный откат будет завершен, вы можете получить ошибку тайм-аута блокировки. Вот что случилось со мной. Решение было просто немного подождать.
Подробности:
Я выполнил запрос DELETE, чтобы удалить около 900 000 из примерно 1 миллиона строк.
Я запустил это по ошибке (удаляет только 10% строк):DELETE FROM table WHERE MOD(id,10) = 0
Вместо этого (удаляет 90% строк):DELETE FROM table WHERE MOD(id,10) != 0
Я хотел удалить 90% строк, а не 10%. Поэтому я убил процесс в командной строке MySQL, зная, что он откатит все строки, которые он удалил до сих пор.
Затем я сразу же выполнил правильную команду и получил lock timeout exceeded ошибка вскоре после. Я понял, что замок на самом деле может быть rollback убитого запроса все еще происходит в фоновом режиме. Поэтому я подождал несколько секунд и снова запустил запрос.
Количество строк невелико... Создайте индекс для account_import_id, если это не первичный ключ.
CREATE INDEX idx_customer_account_import_id ON customer (account_import_id);
Я пришел из Google, и я просто хотел добавить решение, которое работает для меня. Моя проблема заключалась в том, что я пытался удалить записи огромной таблицы, в которой было много FK в каскаде, поэтому я получил ту же ошибку, что и OP.
Я отключил autocommit а потом это сработало просто добавив COMMIT в конце предложения SQL. Насколько я понял, это освобождает буфер по битам вместо ожидания в конце команды.
Чтобы соответствовать примеру OP, это должно сработать:
mysql> set autocommit=0;
mysql> update customer set account_import_id = 1; commit;
Не забудьте активировать autocommit еще раз, если вы хотите оставить конфигурацию MySQL, как и раньше.
mysql> set autocommit=1;
Это крайний случай, но именно такая ошибка может возникнуть, если на томе, на котором находится база данных, не хватает места. Я нашел эту страницу в поисках решения, но ничего не работало. Затем я заметил, что другие вещи на сервере ведут себя странно. Команда df -h показала использование на 100%. Я выключил экземпляр, увеличил размер тома и перезапустил экземпляр, и проблема решилась сама собой. Это AWS EC2. ЮММВ.
Убедитесь, что в таблицах базы данных используется механизм хранения InnoDB и уровень изоляции транзакции READ-COMMITTED.
Вы можете проверить это, выбрав SELECT @@GLOBAL.tx_isolation, @@tx_isolation; на консоли MySQL.
Если он не настроен для чтения, то вы должны установить его. Перед установкой убедитесь, что у вас есть привилегии SUPER в mysql.
Вы можете получить помощь по http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html.
Установив это, я думаю, что ваша проблема будет решена.
Вы также можете проверить, что не пытаетесь обновить это сразу в двух процессах. Пользователи ( @tala) сталкивались с подобными сообщениями об ошибках в этом контексте, возможно, еще раз проверьте, что...
Я столкнулся с подобной проблемой, когда проводил некоторые тесты.
Причина . В моем случае транзакция не была зафиксирована из моего весеннего загрузочного приложения, потому что я убил функцию @transactional во время выполнения (когда функция обновляла некоторые строки). Из-за чего транзакция никогда не была зафиксирована в базе данных (MySQL).
Результат - невозможно обновить эти строки из любого места. Но может обновлять другие строки таблицы.
mysql> update some_table set some_value = "Hello World" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Решение - убил все процессы MySQL, используя
- sudo killall -9 mysqld
- sudo killall -9 mysqld_safe (перезапуск сервера при возникновении ошибки и запись информации о времени выполнения в журнал ошибок. В моем случае не требуется)
Что ж, в MySQL 8 мы можем сделать следующее, чтобы предотвратить тайм-ауты блокировки:
START TRANSACTION;
SELECT * FROM customer FOR UPDATE SKIP LOCKED;
UPDATE customer SET account_import_id = 1;
COMMIT;
Ключевое слово FOR UPDATE заблокирует выбранные строки, а SKIP LOCKED — пропустит уже заблокированные строки. Если мы выполним это одновременно, тайм-аутов ожидания блокировки не произойдет, и первый из них выполнит задание, не блокируя другой.
Этот подход идеально подходит для того, чтобы избежать тайм-аутов блокировки, и его необходимо применять осторожно и понимать, как он работает.
В моем случае я выполнял ненормальный запрос для исправления данных. Если вы заблокируете таблицы в своем запросе, вам не придется иметь дело с таймаутом блокировки:
LOCK TABLES `customer` WRITE;
update customer set account_import_id = 1;
UNLOCK TABLES;
Это, вероятно, не очень хорошая идея для нормального использования.
Для получения дополнительной информации см.: Справочное руководство по MySQL 8.0
Подобные вещи происходили со мной, когда я использовал выход из PHP-конструкции; в середине транзакции. Затем эта транзакция "зависает", и вам нужно убить процесс mysql (описанный выше с помощью processlist;)
У меня была аналогичная ошибка при использовании python для доступа к базе данных mysql.
Программа python использовала цикл while и for.
Закрытие курсора и ссылки в соответствующей строке решило проблему
https://github.com/nishishailesh/sensa_host_com/blob/master/sensa_write.py , см. строку 230.
Похоже, что запрос повторной ссылки без закрытия предыдущей ссылки привел к этой ошибке .
Поздно к вечеринке (как обычно), однако, моя проблема заключалась в том, что я написал какой-то плохой SQL (будучи новичком), и у нескольких процессов была блокировка записи (записей) <- не уверен в правильном словесном выражении. Я закончил тем, что просто: SHOW PROCESSLIST а затем убить идентификаторы с помощью KILL <id>
Я столкнулся с этим, имея 2 соединения с Doctrine DBAL, одно из которых не транзакционное (для важных журналов), они предназначены для параллельной работы, не зависящей друг от друга.
CodeExecution(
TransactionConnectionQuery()
TransactionlessConnectionQuery()
)
Мои тесты интеграции были свернуты в транзакции для отката данных после самого теста.
beginTransaction()
CodeExecution(
TransactionConnectionQuery()
TransactionlessConnectionQuery() // CONFLICT
)
rollBack()
Моим решением было отключить транзакцию обертывания в этих тестах и сбросить данные БД другим способом.
В моем сценарии не удалось выполнить фиксацию после обновления или удаления.
показать список процессов
показал длинный список спящих соединений.
Вчера мы столкнулись с этой проблемой, и после того, как мы рассмотрели практически все предлагаемые решения здесь, а также несколько других из других ответов / форумов, мы в конечном итоге решили ее, как только поняли реальную проблему.
Из-за плохого планирования наша база данных хранилась на смонтированном томе, который также получал наши регулярные автоматические резервные копии. Этот том достиг максимальной емкости.
Как только мы освободили место и перезапустились, эта ошибка была устранена.
Обратите внимание, что мы также вручную уничтожили несколько процессов:
В целом, мы пришли к выводу, что было невероятно неприятно, что ни в одном из наших журналов или предупреждений прямо не упоминается нехватка места на диске, но это, похоже, было основной причиной.
Запуск этой команды сработал для меня
sudo service mysql restart
Если у вас больше ничего не работает и у вас есть собственный экземпляр, я считаю, что было бы эффективнее просто перезапустить MySQL
Была такая же ошибка, хотя я обновлял только одну таблицу с одной записью, но после перезапуска mysql она была исправлена.