修复了“锁定等待超时已超过；尝试重新启动事务"为“卡住" 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部分，因此对表中的所有行都将同一列设置为相同的值，这已经完成了数千次，并且对该列进行了索引，因此相应的索引也可能也被更新了.很多次.

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屋！