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
Recently, when I was writing a WeChat applet, the...
Preface Samba is a free software that implements ...
Check what is installed in mysql rpm -qa | grep -...
In the process of database operation, it is inevi...
I have written many projects that require changin...
I don’t know why, but UI likes to design honeycom...
Note: It is recommended that the virtual machine ...
Table of contents 1. Introduction 2. Main text 2....
Recorded the download and installation tutorial o...
This article describes how to configure a seconda...
vmware workstations starts the virtual machine er...
Preface Named slots are bound to elements using t...
1. Dynamic Components <!DOCTYPE html> <h...
Table of contents Overview Same Origin Policy (SO...
This article example shares the specific code of ...