MySQL kill command usage guide

MySQL kill command usage guide
KILL [CONNECTION | QUERY] processlist_id

In MySQL, each connection runs as a separate thread. You can use the KILL processlist_id statement to terminate statement execution.

KILL allows optional CONNECTION or QUERY modifiers:

  • KILL CONNECTION KILL is the same as no modifier: terminate the connection associated with the given processlist_id, after terminating any statements being executed by that connection.
  • KILL QUERY terminates the statement currently executing on the connection but leaves the connection itself intact.

Use show processlist to view all IDs

Kill command usage

If our application executes SQL, and the execution of SQL is stuck in the database due to lock select for update or too large a number, what should we do if we want to cancel the SQL? We can use the kill command to stop the mysql thread or cancel the SQL execution. Should we execute the kill threadId command or the kill query theadId command?

The biggest difference between kill and kill query is whether to cancel all SQLs executed on the connection, that is, whether to close the thread. If the thread is closed, it corresponds to the statement closing in JDBC.

-- The database locks the record with acctno=13 and executes the following update statement: update test set acctname ='12' where acctno=13

Show processlist to view the thread ID that is executing SQL

If you want to cancel the SQL execution, you can use the command kill query 407 to cancel the SQL execution. After the execution, the 407 thread will not disappear. If there is SQL execution on the connection, it will continue to execute; but if you use kill 407, the 407 thread will disappear. Please pay attention to the difference between the two when using.

In addition to viewing the thread ID through show processlist, you can also use programmatic methods to obtain threadId

 Connection connection = getConnection();
 ((MysqlConnection)connection).getSession().getThreadId();

Statement cancel method

When we use JDBC programming to operate the database, we can also use the cancel method of the Statement object to cancel. The MySQL driver also sends the Kill query threadId instruction internally. The source code of the MySQL driver cancel method

 public void cancel() throws SQLException {
    try {
      if (this.query.getStatementExecuting().get()) {
        if (!this.isClosed && this.connection != null) {
          JdbcConnection cancelConn = null;
          Object cancelStmt = null;

          try {
            HostInfo hostInfo = this.session.getHostInfo();
            String database = hostInfo.getDatabase();
            String user = StringUtils.isNullOrEmpty(hostInfo.getUser()) ? "" : hostInfo.getUser();
            String password = StringUtils.isNullOrEmpty(hostInfo.getPassword()) ? "" : hostInfo.getPassword();
            NativeSession newSession = new NativeSession(this.session.getHostInfo(), this.session.getPropertySet());
            newSession.connect(hostInfo, user, password, database, 30000, new TransactionEventHandler() {
              public void transactionCompleted() {
              }

              public void transactionBegun() {
              }
            });
            //The driver uses KILL QUERY + threadId command to cancel newSession.sendCommand((new NativeMessageBuilder()).buildComQuery(newSession.getSharedSendPacket(), "KILL QUERY " + this.session.getThreadId()), false, 0);
            this.setCancelStatus(CancelStatus.CANCELED_BY_USER);
          } catch (IOException var13) {
            throw SQLExceptionsMapping.translateException(var13, this.exceptionInterceptor);
          finally
            if (cancelStmt != null) {
              ((Statement)cancelStmt).close();
            }

            if (cancelConn != null) {
              ((JdbcConnection)cancelConn).close();
            }

          }
        }

      }
    } catch (CJException var15) {
      throw SQLExceptionsMapping.translateException(var15, this.getExceptionInterceptor());
    }
  }

Client tool execution

The client tool cancels the execution after executing SQL. Some tools send kill commands and some send kill query instructions. You can use the packet capture tool to verify what instructions the Navicat tool sends. The recommended packet capture tool is Wireshark, which is very powerful.

It is a kill command, and the corresponding thread ID is 407. Some tools also send a kill query command.

The above is the detailed content of the MySQL kill command usage guide. For more information about the MySQL kill command, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Detailed explanation of the execution principle of MySQL kill command
  • Mysql accidental deletion of data solution and kill statement principle
  • Mysql uses the kill command to solve the deadlock problem (kill a certain SQL statement being executed)
  • Solution to MySQL Slave triggering oom-killer
  • MySQL OOM Series 3: Get rid of the bad luck of MySQL being killed
  • MySQL OOM System 2 OOM Killer
  • percona-toolkit's pt-kill method to kill mysql queries or connections
  • Batch kill sqls that run for a long time in mysql
  • Reasons why MySQL kill cannot kill threads

<<:  Getting Started: A brief introduction to HTML's basic tags and attributes

>>:  Detailed explanation of monitoring Jenkins process based on zabbix

Recommend

How to deploy HTTPS for free on Tencent Cloud

Recently, when I was writing a WeChat applet, the...

Complete steps to use samba to share folders in CentOS 7

Preface Samba is a free software that implements ...

How to uninstall MySQL 5.7 on CentOS7

Check what is installed in mysql rpm -qa | grep -...

Mysql specifies the date range extraction method

In the process of database operation, it is inevi...

Modify the default scroll bar style in the front-end project (summary)

I have written many projects that require changin...

Sample code for implementing honeycomb/hexagonal atlas with CSS

I don’t know why, but UI likes to design honeycom...

Methods and steps for deploying GitLab environment based on Docker

Note: It is recommended that the virtual machine ...

Detailed explanation of using MySQL where

Table of contents 1. Introduction 2. Main text 2....

MySQL 8.0.12 installation and configuration graphic tutorial

Recorded the download and installation tutorial o...

Basic usage examples of Vue named slots

Preface Named slots are bound to elements using t...

Detailed explanation of JS homology strategy and CSRF

Table of contents Overview Same Origin Policy (SO...

Implementing a simple timer in JavaScript

This article example shares the specific code of ...