修复“超出锁定等待超时；尝试重新启动事务"对于“卡住"mysql表? [英] Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

问题描述

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

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

update some_table set some_column = some_value

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

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.

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

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

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

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?

推荐答案

我通过删除表并从备份恢复它解决了这个问题.

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

这篇关于修复“超出锁定等待超时；尝试重新启动事务"对于“卡住"mysql表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！