SQL: Interrupting a query

Question

I've worked on a project using a proprietary non-SQL DB where queries could be interrupted and in the codebase there were quite some spots where that functionnality was used and made perfect sense (for example to stop a long running query that gets cancelled by the user, or when a more recent query takes place and renders the previous query obsolete, etc.) and I realized I never really saw that kind of "interrupted queries" previously and thought it could make a good SO question (several questions, but they're all related to exactly the same thing):

• can SQL queries be interrupted?

• is this part of the SQL standard?

• if it's not part of the SQL standard, which SQL DBs allow queries to be interrupted (any example most welcome)?

• is it common to interrupt a DB query (SQL or not) which you'll know you won't care about the result anymore? (in the codebase I've worked on, it sure helps lighten the server's load)

Answer 1

Imho "interrupted" should be replaced by 'killed' or 'terminated'. The concept of interrupting can be confusing as one might presume it would allow the query to be resumed later.

The SQL standard does not supply a way to interrupt or terminate a running query, but every DBMS I know does implement a KILL-command or similar. For example, in MySQL a user can use the SHOW [FULL] PROCESSLIST to view all running queries (and their states, query IDs, etc). Users with the KILL privilege can then terminate a query.

Most KILLs happen because a query risks running too long or is blocking other queries, eg. the table is missing an index or the disk is full. When you don't care about the result (eg. user cancelled site navigation), often the webserver itself will abort the process and hence the query in itself (no manual or programmer interaction necessary)

Answer 2

All RDBMS access layers I've ever worked with provide a cancellation method to asynchronously cancel running queries. Check the documentation for whatever data access technology stack you are using. .NET/ADO/JDBC provide a 'cancel' method. ODBC - SQLCancel. Obviously underlying RDBMS vendors data access driver must also implement the method.

In terms of usefullness of cancellation I would tend to be critical of any scheme that made regular use of it. In my opinion better coordination and or design would tend to mitigate non-administrative need.

There is significant dependance on the internals of the RDBMS, nature of the transaction and isolation scheme. If the RDBMS uses an optimistic concurrency model (ie commit is essentially free) canceling a running query can involve a potentially expensive rollback operation. In a worst case a query running for an hour up to the point of cancellation may very well take another hour to rollback.