Query is locking tables, can't kill that process

mysql deadlock database-deadlocks kill-process

45,310

Solution 1

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 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)

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

Solution 2

The KILL command requests the query terminate, and the state of the command should show up as Killed. There's no way to force-kill something and have it terminate immediately.

As a last resort you can always shut-down and restart your mysqld server process.

Solution 3

You need to run following command to kill the process.

> show processlist;  
> kill query processId;

Query parameter specifies that we need to kill query command process.

The syntax for kill process as follows

KILL [CONNECTION | QUERY] processlist_id

Please refer this link for more information.

Solution 4

you should try to kill the mysql/sql service on you computer first and than tray to kill the the program you are runnen white the query.

hope it will work for you

View more solutions

45,310

Author by

Mike

Updated on July 19, 2022

Comments

Mike almost 2 years
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.

I tried to do:
```
show processlist
```
then kill the process id but i can't seems to kill it

I tried:
```
kill 9588
```
9588 is the process id.

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?
Mike about 11 years

okay this killed the query. Thanks But I am hitting dead locks!
Robert Heeren about 11 years

maybe i could help you with your query if you tell me a little more about your database and what your trying to do. if you didn't solved it jet.
mopo922 almost 9 years

This answer could be better by providing a sample command to kill mysql.
fideloper almost 8 years

Official docs on that: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…
Suresh Velusamy about 5 years

Snippet for quickly create stored procedures : Select concat('CALL mysql.rds_kill(',id,');') from information_schema.processlist;