CODEWITHSUNDEEP
sqlmysqlkillinterruptmysqladmin
NoozNooz42
NoozNooz42

Reputation: 4338

SQL: Interrupting a query

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):

Upvotes: 7

Views: 3092

Answers (2)

Konerak
Konerak

Reputation: 39773

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)

Upvotes: 6

Einstein
Einstein

Reputation: 4538

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.

Upvotes: 6

Related Questions