CANCEL QUERY

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 and CANCEL 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. User that are not members of the admin role can cancel only their own currently active queries.

Synopsis

CANCELQUERYIFEXISTSquery_idQUERIESIFEXISTSselect_stmt

Parameters

ParameterDescription
query_idA 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_stmtA 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 QUERIES statement to get the ID of a query and then pass the ID into the CANCEL QUERY statement:

  1. > SHOW QUERIES;
  1. +----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  2. |             query_id             | node_id | username |              start               |              query               |   client_address   | application_name | distributed |   phase   |
  3. +----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  4. | 14dacc1f9a781e3d0000000000000001 |       2 | mroach   | 2017-08-10 14:08:22.878113+00:00 | SELECT * FROM test.kv ORDER BY k | 192.168.0.72:56194 | test_app         | false       | executing |
  5. +----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  6. | 14dacc206c47a9690000000000000002 |       2 | root     | 2017-08-14 19:11:05.309119+00:00 | SHOW CLUSTER QUERIES             | 127.0.0.1:50921    |                  | NULL        | preparing |
  7. +----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  1. > CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

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:

  1. > CANCEL QUERY (SELECT query_id FROM [SHOW CLUSTER QUERIES]
  2.       WHERE client_address = '192.168.0.72:56194'
  3.           AND username = 'mroach'
  4.           AND query = 'SELECT * FROM test.kv ORDER BY k');

Note:
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.

See also

Was this page helpful?
YesNo