MySQL Session - Kill query to unlock table

Question

A query has locked a table in MySQL. How can the running query's session be killed to unlock the table?

I do not know to view the active sessions/processes in MySQL. How can I this via SSH?

Answer 1

Go into PuTTY, and then log into MySQL. Run the following in MySQL:

show processlist;

That will show a list of all running processes. You will probably be able to find the query that is locking your tables as it will likely be the longest running query with a bunch of other queries waiting for the lock release. Make a note of the process id of this query.

Then run:

kill [PROCESSID];

That will kill the process. Of course you need to do this as the user that has privilege to stop the query that was started (so use the same user or root if you have to).

Answer 2

Use a MySQL console or some other tool to run the query SHOW PROCESSLIST to see the active query.

And the run the query kill 123 to kill a query/connection with id 123.