修复“Lock wait timeout exceeded; try restarting transaction”问题，解决Mysql表格“卡住”的情况？

Question

我从一个脚本中向我的本地数据库发送了成千上万次类似于这样的查询：

update some_table set some_column = some_value

我忘记添加where条件，所以同一列的所有行都被设置为相同的值，并且这样做了成千上万次，该列已经创建索引，因此相应的索引可能也被更新了很多次。

我注意到有些问题，因为它花费的时间太长，所以我终止了脚本。即使我重新启动计算机，但某些内容仍然存在于表中，因为简单查询需要很长时间才能运行，当我尝试删除相关索引时，会出现以下消息:

Lock wait timeout exceeded; try restarting transaction

这是一个InnoDB表，所以事务卡住可能是隐式的。我该如何修复这个表并将卡住的事务从中移除？

- Tom

---

SHOW FULL PROCESSLIST 的输出是什么？ - Wolph

相关问题：如何调试锁等待超时？ - Amir Ali Akbari

我有同样的问题，只不过我的所有数据都备份在XML文件中。这些解决方案对我都不起作用（它们往往假定系统正在运行，而不是重新启动），所以我想我必须等待通过“load xml local infile”恢复数百万条记录。[叹气] - ndm13

有趣的是，你的问题得到了 45 个赞，但你的答案却有 -48 个踩 :) - John Demetriou

类似问题的绝佳答案已在http://dba.stackexchange.com/questions/100984/mysql-lock-wait-timeout-exceeded-try-restarting-transaction中列出。 - Matt

Answer 1

我遇到了类似的问题，并通过检查正在运行的线程来解决它。在mysql命令行界面中使用以下命令查看正在运行的线程：

SHOW PROCESSLIST;

如果您没有使用mysql命令行界面的权限，也可以从phpMyAdmin发送该命令。
这将显示一个带有相应ID和执行时间的线程列表，因此您可以终止执行时间过长的线程。
在phpMyAdmin中，您可以使用KILL按钮停止线程，如果您使用命令行界面，则只需使用KILL命令后跟线程ID，例如以下示例：

KILL 115;

这将终止相应线程的连接。

- Mihai Crăiță

---

注意！这是在许多SO线程中提到的一个人提到的：有时候锁定表的进程会在进程列表中显示为睡眠状态！我一直很苦恼，直到我杀掉了所有打开的与问题数据库相关的线程，无论它们是否处于睡眠状态。最终解锁了表并让更新查询运行。评论者提到了类似“有时候MySQL线程会锁定一个表，然后处于睡眠状态，等待非MySQL相关事件发生。”的东西。 - Eric L.

（我在相关问题的这里添加了自己的答案，以充实那个思维碎片。） - Eric L.

这对我很有帮助。我有几个由PHPStorm数据库管理/查询功能创建的“睡眠”线程。 - Ejaz

太好了！我有一个隐藏的进程已经存在5个小时了，现在我终于能够找到并结束它了。 - Aldo Paradiso

这并不是一种解决方法，就像用胶带贴在感染的伤口上并不是一种解决方法。你没有解决根本问题。 - user2914191

我的情况也是如此。我的查询卡住了，只有在我杀死所有的“Sleep”进程后，查询才能执行。有人可以解释一下为什么吗？ - Volodymyr Bilovus

Answer 2

您可以使用以下方法检查当前正在运行的交易：

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

你的交易应该是列表中最早的之一。现在只需要从 trx_mysql_thread_id 中获取值并将其发送给 KILL 命令：

KILL 1234;

如果您不确定哪个交易是您的，请经常重复第一个查询，看哪些交易持续存在。

- nlsrchtr

---

你可能需要使用root账户来运行该SQL语句，以查看哪个事务实际上正在阻止其他人访问表。 - tom10271

Answer 3

检查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) 

- martoncsukas

---

非常感谢！运行SELECT * FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id揭示了罪魁祸首：锁定线程来自我的IP地址...我忘记关闭一个调试控制台，而我在事务进行中离开了它... - Elias Strehle

INNODB_LOCKS表从MySQL 5.7.14版本开始被弃用，并在MySQL 8.0中被移除。请改用SELECT * FROM performance_schema.data_locks;来查询。 - Tyler

Answer 4

当我的数据库大小增长并且我对其进行了大量事务处理时，这种情况开始发生在我身上。

实际上，可能有一些方法可以优化您的查询或数据库，但请尝试使用以下两个查询来解决问题。

运行以下命令：

SET GLOBAL innodb_lock_wait_timeout = 5000; 

然后是这个：

SET innodb_lock_wait_timeout = 5000; 

- Max D

---

参考链接：innodb_lock_wait_timeout - culix

我正在运行一个非常繁忙的11GB数据库。这是唯一对我有效的方法，谢谢！ - dongemus

只需记住，如果您不想永久保留它们，就将值更改为其先前的值。 - Ricardo Martins

这意味着我的一些用户将会有更慢的体验，对吗？ - Arnold Roa

Answer 5

当你建立一个事务连接时，在执行该事务之前，需要获得一把锁。如果无法获取锁，则会尝试一段时间。如果仍然无法获取锁，则会抛出锁等待时间超出错误。无法获取锁的原因是你没有关闭连接。因此，当你尝试第二次获取锁时，由于先前的连接仍未关闭且持有锁，你将无法获得锁。

解决方案： 关闭 连接或根据你的设计 setAutoCommit(true) 来释放锁。

- user3388324

Answer 6

重新启动MySQL，它可以正常工作。

但是要注意，如果这样的查询卡住了，那么就会有问题：

• 您的查询中存在问题（错位的字符、笛卡尔积等）
• 需要编辑非常多的记录
• 复杂的连接或测试（MD5、子字符串、LIKE %...% 等）
• 数据结构问题
• 外键模型（链式/循环锁定）
• 索引不正确的数据

正如@syedrakib所说的，它可以工作，但这对于生产来说不是一个长久的解决方案。

请注意：重新启动可能会影响您的数据，导致状态不一致。

此外，您可以使用EXPLAIN关键字检查MySQL处理查询的方式，看看是否有可能加快查询速度（索引、复杂测试等）。

- Benj

---

谢谢。虽然您没有直接解决我的问题，但我遇到了表锁问题，情况非常糟糕。这是整个互联网上唯一的帖子，它让我想到检查外键。所以我发现主键的键是11，而外键是10。我不知道为什么会发生这种情况，以及为什么之前一切都正常。 - EscapeNetscape

@EscapeNetscape 不用谢，我很高兴这个小回答能帮到你 :) - Benj

Answer 7

进入MySQL的进程。

这样就可以看到仍在运行的任务。

杀死特定的进程或等待进程完成。

- Shemeer M Ali

---

它解决了问题。但这不适用于生产服务器......我们如何输出这个死锁处理？或者如何避免发生这种死锁？ - Rakib

事实上，即使使用完美的格式和完美转义的MySQL查询（这些查询甚至不是由开发人员编写而是由框架的活动记录接口编写），仍然可能出现锁等待超时问题。因此，我认为编写正确的MySQL查询在这里并不是一个因素。 - Rakib

Answer 8

我遇到了一个“更新”语句的相同问题。我的解决方案是简单地运行phpMyAdmin中可用的操作来优化、清除和碎片整理表格（不按顺序）。对于我来说，没有必要删除表格并从备份中恢复它。 :)

- The Science Boy

---

它可能会解决问题。但每次出现此类错误都要这样做，对于LIVE生产服务器来说不是解决方案......我们如何输出这个死锁处理？或者我们如何避免发生这种死锁？ - Rakib

Answer 9

在我的情况下存在的问题：在一个事务中对一些行进行了更新，但在事务提交之前，在另一个地方，这些相同的行正在事务外部被更新。确保所有对行的更新都在同一个事务中完成解决了我的问题。

- Anushri HV

---

这并没有回答OP的问题，因为最初很明显使用情况是在多个不相关的事务中错误地运行多个更新。 - GullerYA

Answer 10

我在尝试删除一组记录时遇到了问题（使用带有ODBC连接到Web服务器上的MySQL的MS Access 2007）。通常，我会从MySQL中删除某些记录，然后用更新的记录替换它们（级联删除几个相关记录，这简化了删除单个记录的所有相关记录）。

我尝试运行phpMyAdmin中可用的操作（优化，刷新等），但是当我尝试刷新时，我收到了需要权限重新加载的错误。由于我的数据库位于Web服务器上，因此无法重启数据库。从备份中恢复不是一个选项。

我尝试在Web上的cPanel mySQL访问中运行此组记录的删除查询。得到相同的错误消息。

我的解决方案：我使用Sun（Oracle）的免费MySQL Query Browser（我之前安装在计算机上）并在那里运行了删除查询。它立即起作用，问题解决了。然后，我能够再次使用ODBC Access到MySQL连接使用Access脚本执行该功能。

- Mike Lane