获取“超过锁定等待超时；尝试重启事务"即使我没有使用交易 [英] Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction

问题描述

我正在运行以下 MySQL UPDATE 语句:

I'm running the following MySQL UPDATE statement:

mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我没有使用交易，为什么会出现这个错误?我什至尝试重新启动我的 MySQL 服务器，但没有帮助.

I'm not using a transaction, so why would I be getting this error? I even tried restarting my MySQL server and it didn't help.

该表有 406,733 行.

The table has 406,733 rows.

推荐答案

你正在使用一个事务；autocommit 不会禁用事务，它只是让它们在语句结束时自动提交.

You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statement.

发生的情况是，某个其他线程在某条记录上持有记录锁(您正在更新表中的每条记录！)时间过长，并且您的线程正在超时.

What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.

您可以通过发出来查看事件的更多详细信息

You can see more details of the event by issuing a

SHOW ENGINE INNODB STATUS

事件之后(在 SQL 编辑器中).最好在安静的测试机器上执行此操作.

after the event (in SQL editor). Ideally do this on a quiet test-machine.

这篇关于获取“超过锁定等待超时；尝试重启事务"即使我没有使用交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！