修正“超过锁定等待超时; 尝试重新启动事务”对于一个“卡住”的 Mysql 表？

通过一个脚本，我向本地数据库发送了数千次这样的查询:

update some_table set some_column = some_value

我忘了添加 where 部分，所以同一列被设置为表中所有行的相同值，这样做了数千次，并且该列被索引，所以相应的索引可能被更新了太多次。

我注意到有些不对劲，因为花了太长时间，所以我把剧本删掉了。从那以后我甚至重新启动了我的电脑，但是有些东西卡在了表格里，因为简单的查询需要很长的时间来运行，当我试图删除相关的索引时，它失败了，这个消息:

Lock wait timeout exceeded; try restarting transaction

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

416171 次浏览
小开
最佳答案

我解决了这个问题，放下桌子，从备份恢复它。

小开

我在“ update”语句中遇到了同样的问题。我的解决方案只是运行 phpMyAdmin 中针对表的操作。我对表进行了优化、刷新和碎片整理(不是按照这个顺序)。没有必要删除表，并恢复从备份为我。:)

小开

我也有同样的问题。我认为这是 SQL 的死锁问题。您只需要从任务管理器强制关闭 SQL 进程。如果修不好，重新启动你的电脑。您不需要删除表并重新加载数据。

小开

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

我尝试运行 phpMyAdmin 中可用于表的操作(优化、刷新等) ，但是当我尝试刷新时，我获得了 RELOAD 错误的需要权限。因为我的数据库在网络服务器上，所以我无法重启数据库。不能从备份还原。

我尝试在 web 上的 cPanel mySQL 访问上运行这组记录的删除查询。收到同样的错误信息。

我的解决方案: 我使用 Sun (Oracle)的免费 MySQL 查询浏览器(我以前安装在我的电脑上)并在那里运行删除查询。很快就奏效了，问题解决了。然后，我能够再次使用 Access 脚本使用 ODBC 访问 MySQL 连接来执行该函数。

小开

在 mysql 中进行 Goto 进程。

所以可以看到还有任务在工作。

终止特定进程或等待进程完成。

小开

重启 MySQL，它工作正常。

但是 要注意，如果这样的查询卡住了，那么在某个地方就会出现问题:

  • 在您的查询中(错放的字符，笛卡儿积，...)
  • 需要编辑的记录很多
  • 复杂连接或测试(MD5、子字符串、 LIKE %...%等)
  • 数据结构问题数据结构问题
  • 外键模型(链/环锁定)
  • 索引错误的数据

正如@syedrakib 所说，这种方法有效，但对于生产来说，这不是长久之计。

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

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

小开

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

SHOW PROCESSLIST;

如果不能访问 mysql 命令行界面，也可以从 phpMyAdmin 发送。
这将显示一个具有相应 id 和执行时间的线程列表，因此您可以杀死执行时间过长的线程。 在 phpMyAdmin 中，你会有一个通过使用 KILL 停止线程的按钮，如果你使用命令行界面，只需使用 KILL 命令后跟线程 ID，如下例所示:

KILL 115;

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

小开

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

解决方案: 差不多连接或 setAutoCommit(true)(根据您的设计)解锁。

小开

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

事实是，可能有一些方法来优化您的查询或您的数据库，但尝试这2个查询周围的工作解决方案。

运行这个:

SET GLOBAL innodb_lock_wait_timeout = 5000;

还有这个:

SET innodb_lock_wait_timeout = 5000;
小开

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

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

您的事务应该是第一个事务之一，因为它是列表中最古老的事务。现在只需从 trx_mysql_thread_id获取值并发送 KILL命令:

KILL 1234;

如果您不确定哪个事务是您的事务，请经常重复第一个查询，并查看哪些事务持久存在。

小开

检查锁的 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)
小开

修好了。

确保在查询中没有不匹配的数据类型插入。 我有一个问题，我正在尝试“用户浏览器代理数据”在 VARCHAR(255)和这个锁有问题，但是当我把它改为 TEXT(255)它修复它。

所以很可能是数据类型不匹配。

小开

本例中的问题: 对事务中的某些行进行了一些更新，在提交事务之前，在另一个地方，同样的行在该事务之外进行了更新。Ensuring that all the updates to the rows are made within the same transaction resolved my issue.

小开

在我的情况下，通过将 delete改为 truncate解决了这个问题

问题 - 查询:

delete from Survey1.sr_survey_generic_details
mycursor.execute(query)

修理 查询:

truncate table Survey1.sr_survey_generic_details
mycursor.execute(query)
小开

这种情况发生在我从多个平台访问数据库时，例如从 dbever 和控制面板。在某个时候，河狸卡住了，因此其他面板无法处理额外的信息。解决方案是重新启动数据库的所有访问点。全部关闭，重新启动。


提交答案