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!