Mysql uses the kill command to solve the deadlock problem (killing an SQL statement being executed)

Mysql uses the kill command to solve the deadlock problem (killing an SQL statement being executed)

When using mysql to run certain statements, the deadlock is caused by a 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: Mysql uses the kill command to solve the deadlock problem (killing an SQL statement being executed)

KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld runs in an independent thread. You can use the show processlist statement to check which threads are running and use the KILL thread_id statement to terminate a thread.

KILL allows the optional CONNECTION or QUERY modifier: kill connection is the same as KILL without the modifier: It terminates the CONNECTION related to the given thread_id. Kill query terminates the statement currently being executed, but keeps the connection intact.

If you have the PROCESS permission, you can view all threads. If you have super administrator permissions, 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 terminate threads.

Log on to MySQL first, and then use: show processlist; to view the status of each thread in 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

The preceding figure shows the list of SQL statements currently being executed. Find the id of the statement with the largest resource consumption.

Run the kill command. The command format is as follows:

Kill id;

-Example:

Killed 8358

Kill.