MySQL: KILL process

Whenever we witness queries running longer than their usual execution time, the important thing to look at is the list of processes running on the server at that instance, and we should ensure that no processes is blocking any other process from getting executed.

The KILL command is a useful option to terminate the blocking/non-critical processes, so that we can ensure our desired process runs smoothly. In this article, we will look at illustrations to show the usage of this KILL command.

Syntax of KILL command

The syntax for KILL command will be as follows:

Syntax of KILL

Examples with KILL command

we can just check the traffics with PROCESS grant, and we can delete the traffics once we have the SUPER grant.

Let us imagine that we have session with ID 13. Our code for stopping the session will be as follows:

KILL session

Stop session with STATUSONLY check

We can use KILL WITH STATUSONLY command for checking the rollback status of the session as follows:

KILL session with STATUSONLY.

Stop the unit of work

We can also delete orphaned distributed transaction with KILL command. For instance, we have session with following UOW ‘A3266C33-E351-22DC-AC8A-CA6A351A22AA’. Our code to kill the session is as follows:

KILL distributed transaction

Distribution transactions are those whose transactional state is unknown for it. (UOW – Unit Of Work)

SHOWPROCESS command to check the sessions list

we can execute “SHOW PROCESSLIST” to check the process list before using KILL command.

SHOW PROCESSLIST command in MySQL

Sample output:

SHOW PROCESSLIST output in MySQL

We can see the whole list of connections with their status of execution to proceed with our KILL command. For instance, we can KILL connection ID 58 from the result with following syntax:

KILL session with ID 58 in MySQL

Problems with KILL command

There are some limitations and slowdown of processes during run the KILL command. Let us observe some key points to keep in mind before KILL execution:

• SELECT, ORDER BY and GROUP BY are denied if session is assigned for deleting with KILL command;
• Locking of table(LOCK command) is denied if some session in the table to be deleted;
• KILL assignment is checked after each record DELETE, UPDATE or CHANGE within UPDATE TABLE and DELETE TABLE commands;
• ALTER TABLE command makes KILL assignment to check each set of records from the initial table;
• INSERT DELAYED command will delete all records included, once KILL command is executed.