Home > Database > Mysql Tutorial > body text

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

黄舟
Release: 2017-06-18 10:45:06
Original
2229 people have browsed it

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
Copy after login

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 |
Copy after login

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
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!