MySQL：“超过锁定等待超时”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/64653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: "lock wait timeout exceeded"
提问by Adrian Dunston
I am trying to delete several rows from a MySQL 5.0.45 database:
我正在尝试从 MySQL 5.0.45 数据库中删除几行：
delete from bundle_inclusions;
The client works for a while and then returns the error:
客户端工作一段时间，然后返回错误：
Lock wait timeout exceeded; try restarting transaction
It's possible there is some uncommitted transaction out there that has a lock on this table, but I need this process to trump any such locks. How do I break the lock in MySQL?
可能有一些未提交的事务在这个表上有一个锁，但我需要这个过程来胜过任何这样的锁。如何打破 MySQL 中的锁？
回答by Matt Solnit
I agree with Erik; TRUNCATE TABLE is the way to go. However, if you can't use that for some reason (for example, if you don't reallywant to delete every row in the table), you can try the following options:
我同意埃里克的观点；TRUNCATE TABLE 是要走的路。但是，如果由于某种原因您不能使用它（例如，如果您真的不想删除表中的每一行），您可以尝试以下选项：
- Delete the rows in smaller batches (e.g. DELETE FROM bundle_inclusions WHERE id BETWEEN ? and ?)
- If it's a MyISAM table (actually, this may work with InnoDB too), try issuing a LOCK TABLE before the DELETE. This should guarantee that you have exclusive access.
- If it's an InnoDB table, then afterthe timeout occurs, use SHOW INNODB STATUS. This should give you some insight into why the lock acquisition failed.
- If you have the SUPER privilege you could try SHOW PROCESSLIST ALL to see what other connections (if any) are using the table, and then use KILL to get rid of the one(s) you're competing with.
- 以较小的批次删除行（例如 DELETE FROM bundle_inclusions WHERE id BETWEEN ? and ?）
- 如果它是 MyISAM 表（实际上，这也适用于 InnoDB），请尝试在 DELETE 之前发出 LOCK TABLE。这应该保证您具有独占访问权限。
- 如果是 InnoDB 表，那么在超时发生后，使用 SHOW INNODB STATUS。这应该能让您深入了解锁获取失败的原因。
- 如果您有 SUPER 权限，您可以尝试 SHOW PROCESSLIST ALL 以查看其他连接（如果有）正在使用该表，然后使用 KILL 摆脱与您竞争的连接。
I'm sure there are many other possibilities; I hope one of these help.
我相信还有很多其他的可能性；我希望其中之一有帮助。
回答by Lars Bohl
Linux: In mysql configuration (/etc/my.cnf or /etc/mysql/my.cnf), insert / edit this line
Linux：在mysql配置（/etc/my.cnf或/etc/mysql/my.cnf）中，插入/编辑这一行
innodb_lock_wait_timeout = 50
Increase the value sufficiently (it is in seconds), restart database, perform changes. Then revert the change and restart again.
充分增加值（以秒为单位），重新启动数据库，执行更改。然后恢复更改并重新启动。
回答by Eric Cope
I had the same issue, a rogue transaction without a end. I restarted the mysqld process. You don't need to truncate a table. You may lose data from that rogue transaction.
我有同样的问题，一个没有结束的流氓交易。我重新启动了 mysqld 进程。您不需要截断表。您可能会丢失来自该恶意交易的数据。
回答by Erik
Guessing: truncate table bundle_inclusions
猜测：截断表 bundle_inclusions
