查询正在锁定表，无法终止该进程 [英] Query is locking tables, can't kill that process

问题描述

我在MySQL中有一个查询锁定表(使用InnoDB):

I have a query locking tables in MySQL (using InnoDB):

UPDATE table SET status = 2 WHERE status = 3

此查询在表上造成了死锁，无法绕过它.

This query caused a deadlock on the table and can't bypass it.

我试图做:

show processlist

然后杀死进程ID，但我似乎无法杀死它

then kill the process id but i can't seems to kill it

我尝试过:

kill 9588

9588是进程ID.

9588 is the process id.

然后我执行show processlist，我仍然在列表上看到相同的查询.

Then I do show processlist I still see the same query on the list.

如何强制终止此过程，然后为什么该查询会导致死锁?我该如何解决?

How can I force killing this process and then why would this query cause a dead lock? how can I fix it?

推荐答案

当您在RDS上运行MySQL实例并由于某种原因想要终止线程或查询时，您会发现无法使用KILL或mysqladmin kill，因为您无权这样做.

When you run a MySQL instance on RDS and want to kill a thread or a query for some reason, you’ll find you cannot use KILL or mysqladmin kill because you don’t have a permission to do so.

RDS提供名为mysql.rds_kill和mysql.rds_kill_query的存储过程，它们将分别杀死线程和查询.要杀死线程，请首先使用SHOW PROCESSLIST获取线程列表并找到要杀死的线程的ID.假设线程ID为53512，则使用

RDS provides the stored procedures named mysql.rds_kill and mysql.rds_kill_query which will kill a thread and a query respectively. To kill a thread, first use SHOW PROCESSLIST to get the list of threads and find the id of the thread you want to kill. Assuming the thread id is 53512, then use

CALL mysql.rds_kill(53512)

来源: http://snak .tumblr.com/post/13856391340/killing-a-thread-or-query-of-mysql-running-on-rds

这篇关于查询正在锁定表，无法终止该进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！