mysql kill线程

生产业务发生了死锁，有时候需要kill杀掉一个线程，怎么操作呢？

 

先看看mysql自带的说明：

mysql> ? kill;
Name: 'KILL'
Description:
Syntax:
KILL [CONNECTION | QUERY] processlist_id

Each connection to mysqld runs in a separate thread. You can kill a
thread with the KILL processlist_id statement.

Thread processlist identifiers can be determined from the ID column of
the INFORMATION_SCHEMA PROCESSLIST table, the Id column of SHOW
PROCESSLIST output, and the PROCESSLIST_ID column of the Performance
Schema threads table. The value for the current thread is returned by
the CONNECTION_ID() function.

KILL permits an optional CONNECTION or QUERY modifier:

o KILL CONNECTION is the same as KILL with no modifier: It terminates
  the connection associated with the given processlist_id, after
  terminating any statement the connection is executing.

o KILL QUERY terminates the statement the connection is currently
  executing, but leaves the connection itself intact.

If you have the PROCESS privilege, you can see all threads. If you have
the SUPER privilege, you can kill all threads and statements.
Otherwise, you can see and kill only your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill
commands to examine and kill threads.

*Note*:

You cannot use KILL with the Embedded MySQL Server library because the
embedded server merely runs inside the threads of the host application.
It does not create any connection threads of its own.

URL: http://dev.mysql.com/doc/refman/5.7/en/kill.html
 

英语看着麻烦不要紧，接着看下面：

 

KILL [CONNECTION | QUERY] thread_id
每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。

KILL允许自选的CONNECTION或QUERY修改符：

• · KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
• · KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。

栗子1，kill connection：

session1：

mysql> select sleep(1000);
 

session2:

mysql> show processlist;

 

图上可以看到：session1的sleep(1000)操作，线程ID为2的线程已经被杀死。

 

栗子2，kill query:

session1：

select sleep(1000);

 

session2:

 

以上操作可以看到：使用kill query操作后，终止连接当前正在执行的语句，但是会保持连接的原状。