SQL: query abort

Question

I worked on a project using my own database, other than SQL, where queries could be aborted, and in the code base there were quite a few places where this function was used and made perfect sense (for example, to stop a long query that was canceled by the user or when a more recent request is made and makes the previous request obsolete, etc.), and I realized that I had never seen such “aborted requests” before and thought I could make a good SO question (a few questions, but they all relate to about bottom and the same):

• Can I interrupt SQL queries?

• Is this part of the SQL standard?

• if it is not part of the SQL standard, which SQL servers allow you to interrupt queries (any example is most welcome)?

• Is a DB query interruption common (SQL or not), which, as you know, no longer cares about the result? (in the codebase I worked on, it helps facilitate server loading)

Answer 1

IMHO "interrupted" should be replaced by "killed" or "completed." The concept of interruption can be confusing, as it can be assumed that this will allow the request to be resumed later.

The SQL standard does not provide a way to interrupt or terminate a running query, but each DBMS that I know implements a KILL command or similar. For example, in MySQL, a user can use SHOW [FULL] PROCESSLIST to view all executed queries (and their states, query identifiers, etc.). Users with the KILL privilege can complete the request.

Most KILLs occur because the request has been running too long or is blocking other requests, for example. the table is missing an index or the disk is full. When you do not care about the result (for example, the user canceled the navigation on the site), often the web server itself will interrupt the process and, therefore, the request itself (no interaction with the manual or software is required)

Answer 2

All the RDBMS access levels I have ever worked with provide a cancellation method to asynchronously terminate requests. Check the documentation for any data access technology stack that you use..NET / ADO / JDBC provide a cancellation method. ODBC - SQLCancel. Obviously, the basic RDBMS provider data access driver must also implement this method.

In terms of the usefulness of cancellation, I would be inclined to criticize any scheme that regularly used it. In my opinion, better coordination and or design will be aimed at mitigating non-administrative needs.

There is significant dependence on the internal components of the DBMS, the nature of the transaction, and the isolation scheme. If the RDBMS uses an optimistic concurrency model (i.e., the commit is essentially free), then canceling a running query can include a potentially costly rollback operation. In the worst case, a request that runs within an hour before cancellation may well take another hour to roll back.