Detailed explanation on using kill command to solve deadlock problem in Mysql

This article mainly introduces Mysql related information about using the kill command to solve the deadlock problem (killing a SQL statement being executed). Friends who need it can refer to it

When using mysql to run certain statements, a deadlock will occur due to the large amount of data, which is not reflected. At this time, you need to kill a query statement that is consuming resources. The syntax format of the KILL command is as follows:

KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld is in a Running in independent threads, you can use the SHOW PROCESSLIST statement to see which threads are running, and use the KILL thread_id statement to terminate a thread.

KILL allows optional CONNECTION or QUERY modifiers: KILL CONNECTION is the same as KILL without modifiers: it terminates the connection related to the given thread_id. KILL QUERY terminates the statement currently executing on the connection, but leaves the connection intact.

If you have PROCESS permission, you can view all threads. If you have super administrator privileges, you can terminate all threads and statements. Otherwise, you can only view and terminate your own threads and statements. You can also use the mysqladmin processlist and mysqladmin kill commands to check and kill threads.

First log in to MySQL, and then use: show processlist; to view the status of each thread in the current mysql.

mysql> show processlist; 
+------+------+----------------------+----------------+---------+-------+-----------+---------------------  
| Id  | User | Host         | db       | Command | Time | State   | Info 
+------+------+----------------------+----------------+---------+-------+-----------+---------------------  
| 7028 | root | ucap-devgroup:53396 | platform    | Sleep  | 19553 |      | NULL  
| 8352 | root | ucap-devgroup:54794 | platform    | Sleep  | 4245 |      | NULL 
| 8353 | root | ucap-devgroup:54795 | platform    | Sleep  |   3 |      | NULL 
| 8358 | root | ucap-devgroup:62605 | platform    | query  | 4156 | updating | update t_shop set |

The above shows the list of sql statements currently being executed. Find the id corresponding to the statement that consumes the most resources.

Then run the kill command, The command format is as follows:

kill id; 
- 示例: 
kill 8358

Just kill it.

The above is the detailed content of Detailed explanation on using kill command to solve deadlock problem in Mysql. For more information, please follow other related articles on the PHP Chinese website!