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

The new version of Chrome browser settings allows cross-domain implementation

Preface Currently, the front-end solves cross-dom...

Using react-beautiful-dnd to implement drag and drop between lists

Table of contents Why choose react-beautiful-dnd ...

js to achieve a simple carousel effect

This article shares the specific code of js to ac...

Sample code for implementing image drawer effect with CSS3

As usual, let’s first post the picture effect: Th...

MySQL scheduled database backup operation example

This article describes the example of MySQL sched...

Discussion on default margin and padding values ​​of common elements

Today we discussed the issue of what the margin v...

Detailed steps to install RabbitMQ in docker

Table of contents 1. Find the mirror 2. Download ...

Nginx configuration and compatibility with HTTP implementation code analysis

Generate SSL Key and CSR file using OpenSSL To co...

HTML form tag tutorial (2):

This tutorial introduces the application of vario...

Practice of using SuperMap in Vue

Table of contents Preface Related Materials Vue p...

MySQL cross-database transaction XA operation example

This article uses an example to describe the MySQ...

Centos7 installation of FFmpeg audio/video tool simple document

ffmpeg is a very powerful audio and video process...

A must-read career plan for web design practitioners

Original article, please indicate the author and ...

Vue.js implements simple folding panel

This article example shares the specific code of ...