KILL CONNECTION and KILL QUERY

Syntax

KILL CONNECTION <internal_process_id> [<node_id>]
KILL QUERY <internal_process_id> [<node_id>]
KILL QUERY COMPILE <query_id> [<node_id>]

Arguments

query_id - see note below.
internal_process_id - ID of the thread to kill.
node_id - ID of the node (aggregator) on which the query/thread is running. If the node_id is not specified, the query/thread running on the current node is killed.

Important

You can query the information_schema.MV_PROCESSLIST table for the required parameter values, where the ID field in the table contains the thread/internal process ID and the NODE_ID field contains the ID of the node.

For KILL QUERY COMPILE, get the ID for the query from the LMV_ASYNC_COMPILES table, ID column.

Remarks

internal_process_id - ID of the thread to kill, which can be found by running SHOW_PROCESSLIST.
KILL CONNECTION will disconnect the client associated with internal_process_id. This is the default, meaning that running KILL <> will have the same result.
KILL QUERY allows you to kill a query running on any aggregator in the cluster.
KILL QUERY will not disconnect the client associated with internal_process_id. Instead, the client will receive an error.
Any query running on the connection will be rolled back.
SingleStore checks for the kill bit during any potentially long running loop during query execution and rolls back the query’s transaction if the kill bit is set.
This command should be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore Commands).
Refer to the Permissions Matrix for the required permissions.

Example

SQL

KILL CONNECTION 3123;
Query OK, 0 rows affected (0.00 sec)

Stopping a query compile:

SQL

SELECT * FROM information_schema.lmv_async_compiles;

+--------+--------+--------------------------------------------------------+-------------------+---------+
| ID     | PID    | QUERY_TEXT                                             | TIME_SINCE_SUBMIT | STATUS  |
+--------+--------+--------------------------------------------------------+-------------------+---------+
| 210812 | 899514 | Query (null).'SELECT * FROM accounts WHERE < ... >.  ' |             0.000 | running |
+--------+--------+--------------------------------------------------------+-------------------+---------+

SQL

KILL QUERY COMPILE 210812;

Refer SHOW PROCESSLIST for details on recently run queries.

Audit of Automatic Rollback Events

If a CA is terminated before COMMIT, the open transaction is cleaned up and any uncommitted changes are rolled back.

The following options can be used to trace the implicit rollback however, there is no dedicated user-facing audit trail or event that explicitly records the automatic rollback:

MV_PROCESSLIST view shows active sessions with fields like TRANSACTION_STATE and ROW_LOCKS_HELD but only while the session is still alive, it does not capture events retrospectively.
Query History / MV_TRACE_EVENTS, which show query completions or failures but not the implicit rollback cleanup itself.
Audit logs, which capture executed SQL such as explicit ROLLBACK, but do not record a separate event for automatic rollback due to connection or process cleanup.
Node logs, which may show the CA failure or restart, but not an actual transaction-level rollback record.