MySQL kill directives

KILL [CONNECTION | QUERY] processlist_id
Copy the code

In Mysql, each connection is run on 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: terminates a CONNECTION with a given associationprocesslist_idAfter terminating any statements that the connection is executing.
• KILL QUERY Terminates the currently executing statement of the connection, but leaves the connection itself unchanged.

Use show processList to see all ids

Use the Kill command

If we want to cancel the SQL execution, the SQL execution is stuck in the database because of the lock select for UPDATE or the number is too large, what should we do? To stop the mysql thread or cancel the SQL execution, run the kill threadId command or the kill query theadId command.

The biggest difference between kill and Kill query is whether to cancel all SQL executed in the connection, that is, whether to close the thread. If the thread is closed, the STATEMENT in JDBC is closed

The database locks the acctno=13 record and executes the following update statement
update test set acctname ='12' where acctno=13
Copy the code

Show processList Shows the ID of the thread that is executing the SQL

If you want to cancel the SQL execution, run the kill query 407 command to cancel the SQL execution. After the execution, the 407 thread will not disappear. If the SQL execution is connected, the execution will continue. But if you use kill 407, the 407 thread disappears. Pay attention to the differences between the two when using.

In addition to viewing thread ids through show ProcessList, you can also programmatically get threadId

 Connection connection = getConnection();
 ((MysqlConnection)connection).getSession().getThreadId();
Copy the code

The Statement cancel method

When we use JDBC programming mode to perform database operations, we can also use the Statement object cancel method to cancel, Mysql driver internal also send Kill query threadId instruction, Mysql driver cancel method source code

 public void cancel(a) 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(a) {}public void transactionBegun(a) {}});// The KILL QUERY + threadId command is used inside the driver 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()); }}Copy the code

Client tool execution

The client tool cancels the execution after executing SQL. Some tools send kill commands and others send kill query commands. You can use the packet capture tool to verify the commands sent by Navicat.

Grab a message

Is the kill instruction, the corresponding thread ID is 407, some tools are sent kill query instruction.