Use the KILL statement to terminate a running query or close a session connection in ApsaraDB for SelectDB.
How it works
Every SQL query runs inside a session connection. The KILL statement supports two modes:
KILL QUERY: Terminates only the running query and keeps the session connection open.KILL CONNECTION: Closes the entire session connection, which also terminates any running query in it. This is the default when you runKILLwithout a modifier.
Syntax
KILL [CONNECTION | QUERY] processlist_id| Parameter | Description |
|---|---|
CONNECTION | Closes the session connection and terminates any running query in it. This is the default when no modifier is specified. |
QUERY | Terminates the currently running query but keeps the session connection open. |
processlist_id | The ID of the target session connection. Run SHOW PROCESSLIST to list all IDs, or SELECT CONNECTION_ID() to get your current session ID. |
Find the session connection ID
Before you run KILL, identify the processlist_id of the target session connection.
Get the current session connection ID
Run SELECT CONNECTION_ID() to return the ID of your current session connection:
SELECT connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 48 |
+-----------------+
1 row in set (0.00 sec)List all session connections
Run SHOW PROCESSLIST to display all active session connections:
SHOW PROCESSLIST;
+------------------+------+------+--------------------+---------------------+----------+---------+---------+------+-------+-----------------------------------+---------------------------------------------------------------------------------------+
| CurrentConnected | Id | User | Host | LoginTime | Catalog | Db | Command | Time | State | QueryId | Info |
+------------------+------+------+--------------------+---------------------+----------+---------+---------+------+-------+-----------------------------------+---------------------------------------------------------------------------------------+
| Yes | 48 | root | 10.16.xx.xx:44834 | 2023-12-29 16:49:47 | internal | test | Query | 0 | OK | e6e4ce9567b04859-8eeab8d6b5513e38 | SHOW PROCESSLIST |
| | 50 | root | 192.168.xx.xx:52837 | 2023-12-29 16:51:34 | internal | | Sleep | 1837 | EOF | deaf13c52b3b4a3b-b25e8254b50ff8cb | SELECT @@session.transaction_isolation |
| | 51 | root | 192.168.xx.xx:52843 | 2023-12-29 16:51:35 | internal | | Sleep | 907 | EOF | 437f219addc0404f-9befe7f6acf9a700 | /* ApplicationName=DBeaver Ultimate 23.1.3 - Metadata */ SHOW STATUS |
| | 55 | root | 192.168.xx.xx:55533 | 2023-12-29 17:09:32 | internal | test | Sleep | 271 | EOF | f02603dc163a4da3-beebbb5d1ced760c | /* ApplicationName=DBeaver Ultimate 23.1.3 - SQLEditor <Console> */ SELECT DATABASE() |
| | 47 | root | 10.16.xx.xx:35678 | 2023-12-29 16:21:56 | internal | test | Sleep | 3528 | EOF | f4944c543dc34a99-b0d0f3986c8f1c98 | select * from test |
+------------------+------+------+--------------------+---------------------+----------+---------+---------+------+-------+-----------------------------------+---------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)Key columns in the output:
| Column | Description |
|---|---|
CurrentConnected | Shows Yes for your current session connection. |
Id | The processlist_id to use with the KILL statement. |
Command | The current state: Query (running a query) or Sleep (idle). |
Time | Seconds since the last activity. Useful for identifying long-running or stale connections. |
Info | The SQL statement being executed, or empty if idle. |
Examples
Terminate a running query
To stop a query on session connection 55 without closing the connection:
KILL QUERY 55;
Query OK, 0 rows affected (0.01 sec)The query on connection 55 is terminated, but the connection remains open for subsequent queries.
Close a session connection
To close session connection 55 and terminate any running query in it:
KILL CONNECTION 55;
Query OK, 0 rows affected (0.01 sec)Running KILL 55 without a modifier has the same effect.