Query is locking tables, can't kill that process

mysql deadlock database-deadlocks kill-process
Mike picture Mike · Apr 10, 2013 · Viewed 39.4k times · Source

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?

Answer

Eric Leschinski picture Eric Leschinski · Oct 30, 2014

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

Related questions

Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"

I have a MySQL table with about 5,000,000 rows that are being constantly updated in small ways by parallel Perl processes connecting via DBI. The table has about 10 columns and several indexes. One fairly common operation gives rise to the following …

Read More
How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'

I have a innoDB table which records online users. It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a …

Read More
How can I clear a transaction deadlock?

Using 'show engine innodb status' I see that wordpress has two deadlocks. I'd like to clear these up but I don't see an active process for either of these cmds (IE something to 'kill' and hopefully force a rollback). I …

Read More