mysql清空表超时_为“卡住的”Mysql表修复“超出锁定等待超时;尝试重新启动事务”？...

为“卡住的”Mysql表修复“超出锁定等待超时;尝试重新启动事务”？

从脚本我发送了数千次这样的查询到我的本地数据库：

update some_table set some_column = some_value

我忘了添加where部分，因此同一列被设置为表中所有行的相同值，这已经完成了数千次并且列已被索引，因此相应的索引可能更新了很多次。

我注意到有些事情是错的，因为它花了太长时间，所以我杀了剧本。 从那时起我甚至重新启动了我的计算机，但是表格中存在一些问题，因为简单查询需要很长时间才能运行，当我尝试删除相关索引时，它会失败并显示以下消息：

Lock wait timeout exceeded; try restarting transaction

它是一个innodb表，因此粘滞的交易可能是隐含的。 如何修复此表并从中删除卡住的事务？

Tom asked 2019-07-06T09:25:59Z

12个解决方案

124 votes

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

SHOW PROCESSLIST;

如果您无法访问mysql命令行界面，也可以从phpMyAdmin发送它。

这将显示具有相应ID和执行时间的线程列表，因此您可以杀死执行时间过长的线程。在phpMyAdmin中，您将有一个使用KILL来停止线程的按钮，如果您使用命令行界面，只需使用KILL命令后跟线程ID，如下例所示：

KILL 115;

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

Mihai Crăiță answered 2019-07-06T09:26:39Z

39 votes

您可以使用检查当前正在运行的事务

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

您的交易应该是最早的交易之一，因为它是列表中最早的交易。 现在只需从trx_mysql_thread_id获取值并将其发送到KILL命令：

KILL 1234;

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

nlsrchtr answered 2019-07-06T09:27:19Z

30 votes

当我的数据库大小增加并且我在它上面做了很多事务时，这开始发生在我身上。

事实上，可能有一些方法可以优化您的查询或数据库，但尝试这两个查询来解决修复问题。

运行这个：

SET GLOBAL innodb_lock_wait_timeout = 5000;

然后这个：

SET innodb_lock_wait_timeout = 5000;

Max D answered 2019-07-06T09:27:59Z

26 votes

检查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`;

在调查上面的结果后，您应该能够看到什么是锁定什么。

问题的根本原因可能也在于您的代码 - 如果您使用像Hibernate这样的JPA，请检查相关函数，尤其是注释。

例如，如此处所述，滥用以下注释可能会导致数据库中的锁定：

@Transactional(propagation = Propagation.REQUIRES_NEW)

martoncsukas answered 2019-07-06T09:29:08Z

7 votes

重启MySQL，它运行正常。

但请注意，如果这样的查询被卡住，则存在某个问题：

在您的查询中(错位的char，笛卡尔积，...)

要编辑的记录非常多

复杂的连接或测试(MD5，子串，LIKE %...%等)

数据结构问题

外键模型(链/循环锁定)

错误索引的数据

正如@syedrakib所说，它有效，但这不是生产的长期解决方案。

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

此外，您可以检查MySQL如何使用EXPLAIN关键字处理您的查询，并查看是否有可能加速查询(索引，复杂测试......)。

Benj answered 2019-07-06T09:30:49Z

4 votes

为事务建立连接时，在执行事务之前获取锁定。 如果无法获得锁定，那么你会尝试一段时间。 如果仍然无法获得锁定，则抛出锁定等待时间超出错误。 为什么您无法获得锁定是因为您没有关闭连接。 因此，当您尝试第二次锁定时，您将无法获得锁定，因为您之前的连接仍未关闭并持有锁定。

解决方案：关闭连接或setAutoCommit(true)[根据您的设计]释放锁定。

user3388324 answered 2019-07-06T09:31:22Z

3 votes

转到mysql中的进程。

所以可以看出任务仍然有效。

杀死特定进程或等到进程完成。

Shemeer M Ali answered 2019-07-06T09:32:01Z

1 votes

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

我试图在phpMyAdmin中运行表中的可用操作(优化，刷新等)，但是当我尝试刷新时，我得到RELOAD错误的需要权限。 由于我的数据库在Web服务器上，我无法重新启动数据库。 从备份恢复不是一种选择。

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

我的解决方案：我使用了Sun的免费MySQL查询浏览器(我以前安装在我的计算机上)并在那里运行了删除查询。 它立即起作用，问题解决了。 然后我能够使用ODBC访问MySQL连接使用Access脚本再次执行该功能。

Mike Lane answered 2019-07-06T09:32:49Z

0 votes

我使用＆＃34;更新＆＃34; -statement遇到了同样的问题。 我的解决方案只是运行phpMyAdmin中可用的操作。 我对表进行了优化，刷新和碎片整理(不按此顺序)。 无需删除表并从备份中恢复它。:)

The Science Boy answered 2019-07-06T09:33:13Z

0 votes

我遇到过同样的问题。 我认为这是SQL的死锁问题。 您可以从任务管理器强制关闭SQL进程。 如果没有解决问题，只需重新启动计算机即可。 您不需要删除表并重新加载数据。

kriver answered 2019-07-06T09:33:39Z

0 votes

固定它，确保查询中没有不匹配的数据类型插入。我有一个问题，我正在尝试用户浏览器代理数据＆＃34; in＆＃34; VARCHAR(255)＆＃34; 并且当我将其更改为＆＃34; TEXT(255)＆＃34; 它修复了它。所以很可能是数据类型的错误匹配

Hiren Raj answered 2019-07-06T09:34:05Z

-113 votes

我通过删除表并从备份中恢复来解决了这个问题。

Tom answered 2019-07-06T09:34:30Z