How to use kill command to solve deadlock problem in Mysql

This article introduces Mysql how to use the kill command to solve deadlock problems, the content is very detailed, interested partners can refer to, I hope to help you.

When using mysql to run certain statements, the amount of data is too large to cause deadlocks and is not reflected. At this time, you need to kill a query statement that is consuming resources. The syntax of the KILL command is as follows:

1 KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld runs in a separate thread, and you can use the SHOW PROCESSLIST statement to see which threads are running and 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 associated with the given thread_id. KILL QUERY terminates the connection with the currently executing statement, 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 inspect and terminate threads.

Log in to MySQL first, and then use: show processlist; to view the current status of each thread in MySQL.

1 mysql> show processlist;

2 +------+------+----------------------+----------------+---------+-------+-----------+---------------------

3 | Id | User | Host | db | Command | Time | State | Info

4 +------+------+----------------------+----------------+---------+-------+-----------+---------------------

5 | 7028 | root | ucap-devgroup:53396 | platform | Sleep | 19553 | | NULL

6 | 8352 | root | ucap-devgroup:54794 | platform | Sleep | 4245 | | NULL

7 | 8353 | root | ucap-devgroup:54795 | platform | Sleep | 3 | | NULL

8 | 8358 | root | ucap-devgroup:62605 | platform | query | 4156 | updating | update t_shop set |

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

Then run the kill command in the following format:

1 kill id;

2 -Examples:

3 kill 8358

Just kill it.

Mysql on how to use the kill command to solve the deadlock problem to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.