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?
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
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With