TDSQL·进阶篇·KILL 线程

[weixin_44020914]

在使用数据库运行某些语句时，会因数据量太大而导致死锁，没有反应。这个时候，就需要 KILL 掉某个正在消耗资源的 query 语句即可，KILL 命令的语法格式如下：

KILL [CONNECTION | QUERY] thread_id

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

mysql> show processlist;
+--------+------------+----------------+------+---------+------+------------+---------------------+-----------+---------------+
| Id     | User       | Host           | db   | Command | Time | State      | Info                | Rows_sent | Rows_examined |
+--------+------------+----------------+------+---------+------+------------+---------------------+-----------+---------------+
| 924107 | sutest | 10.0.0.8:38314 | NULL | Query   |    0 | starting   | show processlist    |         0 |             0 |
| 924114 | sutest | 10.0.0.8:38318 | test | Query   |  264 | User sleep | select sleep(20000) |         0 |             0 |
+--------+------------+----------------+------+---------+------+------------+---------------------+-----------+---------------+
2 rows in set (0.00 sec)
mysql> kill 924114;
Query OK, 0 rows affected (0.00 sec)

如果您的业务有较多线程，无法准确判断哪些事务未提交，可以采用类似 SQL 进行查询线程 ID（举例）：

SELECT 
  it.trx_id AS trx_id,
  it.trx_state AS trx_state,
  it.trx_started AS trx_started,
  it.trx_mysql_thread_id AS trx_mysql_thread_id,
  CURRENT_TIMESTAMP - it.trx_started AS RUN_TIME,
  pl.user AS USER,
  pl.host AS HOST,
  pl.db AS db,
  pl.time AS trx_run_time,
  pl.INFO as INFO
FROM
  information_schema.INNODB_TRX it,
  information_schema.processlist pl
WHERE
  pl.id=it.trx_mysql_thread_id
ORDER BY RUN_TIME DESC LIMIT 10;

····更多内容详见：https://cloud.tencent.com/document/product/237/16954

[CSDN-Ada助手]

您可以前往 CSDN问答-大数据 发布问题, 以便更快地解决您的疑问