Help & Documentation>TencentDB for MariaDB

Killing Threads

Last updated: 2023-08-31 09:31:05
When using the database to run certain statements, it will cause a deadlock due to the large amount of data, and there is no response. At this time, you need to kill a query statement that is consuming resources. The syntax of the KILL command is as follows:
    KILL [CONNECTION | QUERY] thread_id
Each connection to mysqld runs in a separate thread. You can use the SHOW PROCESSLIST statement to view the active threads and the KILL thread_id statement to terminate a specific thread.
    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)
If your business has many threads and you cannot accurately determine which transactions have not been committed, you can use an SQL statement similar to the following content to query thread ID (for example):
    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;
If your business has numerous threads and you cannot accurately determine which transactions are in lock waiting, you can use an SQL statement similar to the following to query the thread ID (for example):
    SELECT 
    r.trx_id waiting_trx_id, 
    r.trx_mysql_thread_id waiting_thread, 
    TIMESTAMPDIFF( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) wait_time, 
    r.trx_query waiting_query, 
    l.lock_table waiting_table_lock, 
    b.trx_id blocking_trx_id, 
    b.trx_mysql_thread_id blocking_thread,
    SUBSTRING( p. HOST, 1, INSTR(p. HOST, ':') - 1 ) blocking_host, 
    SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port,
    IF (p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx, 
    b.trx_query blocking_query FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id INNER JOIN information_schema.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id ORDER BY wait_time DESC;
Warning: After a large transaction KILL, the transaction needs to be rolled back, and it will take a long time to wait for a large amount of data. At this time, you can click the master-slave switch on the console to switch the slave as the master to quickly restore the business. But please be aware that when using asynchronous synchronization and strong synchronous (degradable) replication schemes, due to the delay in master-slave data synchronization, some data may be lost/disordered, please operate the master-slave switch carefully.
中国站 - English