The CANCEL QUERY
statement cancels a running SQL query.
Considerations
- Schema changes are treated differently than other SQL queries. You can use
SHOW JOBS
to monitor the progress of schema changes andCANCEL JOB
to cancel schema changes that are taking longer than expected. - In rare cases where a query is close to completion when a cancellation request is issued, the query may run to completion.
Required privileges
Members of the admin
role (include root
, which belongs to admin
by default) can cancel any currently active queries. User that are not members of the admin
role can cancel only their own currently active queries. To view and cancel another non-admin user's query, the user must be a member of the admin
role or must have the VIEWACTIVITY
and CANCELQUERY
parameters set.
Synopsis
Parameters
Parameter | Description |
---|---|
query_id |
A scalar expression that produces the ID of the query to cancel.CANCEL QUERY accepts a single query ID. If a subquery is used and returns multiple IDs, the CANCEL QUERY statement will fail. To cancel multiple queries, use CANCEL QUERIES . |
select_stmt |
A selection query whose result you want to cancel. |
Response
When a query is successfully cancelled, CockroachDB sends a query execution canceled
error to the client that issued the query.
- If the canceled query was a single, stand-alone statement, no further action is required by the client.
- If the canceled query was part of a larger, multi-statement transaction, the client should then issue a
ROLLBACK
statement.
Examples
Cancel a query via the query ID
In this example, we use the SHOW STATEMENTS
statement to get the ID of a query and then pass the ID into the CANCEL QUERY
statement:
SHOW STATEMENTS;
query_id | node_id | session_id | user_name | start | query | client_address | application_name | distributed | phase
-----------------------------------+---------+----------------------------------+-----------+-------------------------------------+--------------------------------------+-----------------+------------------+-------------+------------
1673f58fca5301900000000000000001 | 1 | 1673f583067d51280000000000000001 | demo | 2021-04-08 18:31:29.079614+00:00:00 | SELECT * FROM rides ORDER BY revenue | 127.0.0.1:55212 | $ cockroach demo | true | executing
1673f590433eaa000000000000000001 | 1 | 1673f58a4ba3c8e80000000000000001 | demo | 2021-04-08 18:31:31.108372+00:00:00 | SHOW CLUSTER STATEMENTS | 127.0.0.1:55215 | $ cockroach sql | false | executing
(2 rows)
CANCEL QUERY '1673f590433eaa000000000000000001';
Cancel a query via a subquery
In this example, we nest a SELECT
clause that retrieves the ID of a query inside the CANCEL QUERY
statement:
CANCEL QUERY (SELECT query_id FROM [SHOW CLUSTER STATEMENTS]
WHERE client_address = '127.0.0.1:55212'
AND user_name = 'demo'
AND query = 'SELECT * FROM rides ORDER BY revenue');
CANCEL QUERIES 1
CANCEL QUERY
accepts a single query ID. If a subquery is used and returns multiple IDs, the CANCEL QUERY
statement will fail. To cancel multiple queries, use CANCEL QUERIES
.