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:
Use show processlist to view all IDs Kill command usageIf 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 methodWhen 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 executionThe 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:
|
<<: Getting Started: A brief introduction to HTML's basic tags and attributes
>>: Detailed explanation of monitoring Jenkins process based on zabbix
Preface Currently, the front-end solves cross-dom...
Table of contents Why choose react-beautiful-dnd ...
This article shares the specific code of js to ac...
As usual, let’s first post the picture effect: Th...
This article describes the example of MySQL sched...
Today we discussed the issue of what the margin v...
Table of contents 1. Find the mirror 2. Download ...
Generate SSL Key and CSR file using OpenSSL To co...
This article shares the installation and configur...
This tutorial introduces the application of vario...
Table of contents Preface Related Materials Vue p...
This article uses an example to describe the MySQ...
ffmpeg is a very powerful audio and video process...
Original article, please indicate the author and ...
This article example shares the specific code of ...