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

Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

提问by Tom

From a script I sent a query like this thousands of times to my local database:

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

update some_table set some_column = some_value

I forgot to add the where part, so the same column was set to the same a value for all the rows in the table and this was done thousands of times and the column was indexed, so the corresponding index was probably updated too lots of times.

我忘了添加 where 部分，所以对于表中的所有行，相同的列被设置为相同的 a 值，这已经完成了数千次并且该列被索引，所以相应的索引可能被更新了太多次.

I noticed something was wrong, because it took too long, so I killed the script. I even rebooted my computer since then, but something stuck in the table, because simple queries take a very long time to run and when I try dropping the relevant index it fails with this message:

我注意到有些不对劲，因为它花了太长时间，所以我杀死了脚本。从那以后我什至重新启动了我的计算机，但有些东西卡在了表中，因为简单的查询需要很长时间才能运行，当我尝试删除相关索引时，它会失败并显示以下消息：

Lock wait timeout exceeded; try restarting transaction

It's an innodb table, so stuck the transaction is probably implicit. How can I fix this table and remove the stuck transaction from it?

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

采纳答案by Tom

I solved the problem by dropping the table and restoring it from backup.

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

回答by Mihai Cr?i??

I had a similar problem and solved it by checking the threads that are running. To see the running threads use the following command in mysql command line interface:

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

SHOW PROCESSLIST;

It can also be sent from phpMyAdmin if you don't have access to mysql command line interface.
This will display a list of threads with corresponding ids and execution time, so you can KILL the threads that are taking too much time to execute. In phpMyAdmin you will have a button for stopping threads by using KILL, if you are using command line interface just use the KILL command followed by the thread id, like in the following example:

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

KILL 115;

This will terminate the connection for the corresponding thread.

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

回答by nlsrchtr

You can check the currently running transactions with

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

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

Your transaction should be one of the first, because it's the oldest in the list. Now just take the value from trx_mysql_thread_idand send it the KILLcommand:

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

KILL 1234;

If you're unsure which transaction is yours, repeat the first query very often and see which transactions persist.

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

回答by Max D

This started happening to me when my database size grew and I was doing a lot of transactions on it.

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

Truth is there is probably some way to optimize either your queries or your DB but try these 2 queries for a work around fix.

事实是，可能有某种方法可以优化您的查询或数据库，但请尝试使用这 2 个查询来解决问题。

Run this:

运行这个：

SET GLOBAL innodb_lock_wait_timeout = 5000; 

And then this:

然后这个：

SET innodb_lock_wait_timeout = 5000; 

回答by martoncsukas

Check InnoDB status for locks

检查 InnoDB 状态以获取锁

SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

检查 MySQL 打开的表

SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

检查待处理的 InnoDB 事务

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

Check lock dependency - what blocks what

检查锁依赖 - 什么阻止了什么

SELECT * FROM `information_schema`.`innodb_locks`;

After investigating the results above, you should be able to see what is locking what.

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

The root cause of the issue might be in your code too - please check the related functions especially for annotations if you use JPA like Hibernate.

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

For example, as described here, the misuse of the following annotation might cause locks in the database:

例如，如所描述这里，下面标注的滥用可能会导致数据库锁：

@Transactional(propagation = Propagation.REQUIRES_NEW) 

回答by Benj

Restart MySQL, it works fine.

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

BUTbeware that if such a query is stuck, there is a problem somewhere :

但是请注意，如果这样的查询卡住了，那么某处就会出现问题：

• in your query (misplaced char, cartesian product, ...)
• very numerous records to edit
• complex joins or tests (MD5, substrings, LIKE %...%, etc.)
• data structure problem
• foreign key model (chain/loop locking)
• misindexed data

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

As @syedrakib said, it works but this is no long-living solution for production.

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

Beware : doing the restart can affect your data with inconsistent state.

当心：重新启动可能会影响您的数据状态不一致。

Also, you can check how MySQL handles your query with the EXPLAIN keyword and see if something is possible there to speed up the query (indexes, complex tests,...).

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

回答by user3388324

When you establish a connection for a transaction, you acquire a lock before performing the transaction. If not able to acquire the lock, then you try for sometime. If lock is still not obtainable, then lock wait time exceeded error is thrown. Why you will not able to acquire a lock is that you are not closing the connection. So, when you are trying to get a lock second time, you will not be able to acquire the lock as your previous connection is still unclosed and holding the lock.

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

Solution:closethe connection or setAutoCommit(true)(according to your design) to release the lock.

解决方案：关闭连接或setAutoCommit(true)（根据您的设计）解除锁定。

回答by Shemeer M Ali

Goto processes in mysql.

在 mysql 中转到进程。

So can see there is task still working.

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

Kill the the particular process or wait until process complete.

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

回答by Mike Lane

I had this problem when trying to delete a certain group of records (using MS Access 2007 with an ODBC connection to MySQL on a web server). Typically I would delete certain records from MySQL then replace with updated records (cascade delete several related records, this streamlines deleting all related records for a single record deletion).

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

I tried to run through the operations available in phpMyAdmin for the table (optimize,flush, etc), but I was getting a need permission to RELOAD error when I tried to flush. Since my database is on a web server, I couldn't restart the database. Restoring from a backup was not an option.

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

I tried running delete query for this group of records on the cPanel mySQL access on the web. Got same error message.

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

My solution: I used Sun's (Oracle's) free MySQL Query Browser (that I previously installed on my computer) and ran the delete query there. It worked right away, Problem solved. I was then able to once again perform the function using the Access script using the ODBC Access to MySQL connection.

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

回答by The Science Boy

I ran into the same problem with an "update"-statement. My solution was simply to run through the operations available in phpMyAdmin for the table. I optimized, flushed and defragmented the table (not in that order). No need to drop the table and restore it from backup for me. :)

我在“更新”语句中遇到了同样的问题。我的解决方案只是运行 phpMyAdmin 中可用的表操作。我对表进行了优化、刷新和碎片整理（不是按这个顺序）。无需删除表并为我从备份中恢复它。:)

回答by kriver

I had the same issue. I think it was a deadlock issue with SQL. You can just force close the SQL process from Task Manager. If that didn't fix it, just restart your computer. You don't need to drop the table and reload the data.

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