How to cancel a long-running Database operation?

Question

Currently working with Oracle, but will also need a solution for MS SQL.

I have a GUI that allows users to generate SQL that will be executed on the database. This can take a very long time, depending on the search they generate. I want the GUI/App to responsive during this search and I want the user to be able to cancel the search.

I'm using a Background Worker Thread.

My problem is that, when the user cancels the search, I can't interrupt the call to the database. It waits until it is finished and then, it can poll the 'CancelationPending' property. Not only does this waste resources on the database, but it creates problems for my code.

If the user hits 'Search' on a very long query, then clicks 'Cancel' and then 'Search' again - the first search is still chugging away on the database. The background worker is still busy when they hit search again. The only solution I've got to this problem is to make a new background worker.

It seems like a really ugly way to do things. The database keeps working I'm creating new instances of background workers....when I really want to STOP the database call and re-use the same worker.

How can I do that?

Thanks for the all posts; I'm really not sure which answer is the best; all of the up-vote seem to have come from me. My apologies if I've made the wrong choice. — Rob P., May 20 '09 at 20:04

score 10 · Answer 1 · answered May 20 '09 at 17:36

If you're using ADO.NET and SQL data provider, take a look at SqlCommand.Cancel method. That does what you're looking for. However, it tries to cancel and the cancellation may take time. Basically, it's up to SQL Server to decide when to grant your cancellation request. When the query is cancelled, you should get a SqlException that indicates that the operation was cancelled by user. Apparently, you don't want to treat this exception as exception and handle it specially such as if SqlException is due to user cancelling the operation, just swallow it.

Koen · Answer 2 · 2010-12-10T14:57:06.490

8

I also noticed command.Cancel() doesn't really abort the command. What worked for me is closing the connection (rollback transaction if you use one) when the user aborts. This will raise an exception in your background thread while the command is executing, so you have to catch it and check the CancellationPending property there and not rethrow the exception in that case...

// When aborting
worker.CancelAsync();
command.Connection.Close();

// In your DoWork event handler
...
catch (Exception)
{
    if (worker.CancellationPending)
    {
        e.Cancel = true;
        return;
    }
    else
    {
        throw;
    }
}

// And in your RunWorkerCompleted event handler
if (e.Error == null && !e.Cancelled)
{
    ...
}

edited Dec 10 '10 at 14:57

answered Sep 13 '10 at 10:02

Koen

3,626
1
34
55

1

I am trying to employ this suggestion but get a nativeexception that I cannot catch that prevents me from doing this this way. Do you have any experience with this method using SQLCE? – Matthew Belk Nov 08 '10 at 22:26
1

You'll want to call SqlConnection.ClearPool to ensure the connection doesn't go back to the pool... http://objectmix.com/ado-dao-rdo-rds/358123-sqlconnection-clearpool-best-practice-command-timeout.html http://stackoverflow.com/questions/1145892/how-to-force-a-sqlconnection-to-physically-close-while-using-connection-pooling – user423430 Oct 07 '11 at 23:23
@MatthewBelk: You should be able to catch such exceptions using no filter in your catch clause i.e. catch { // your code }. – ctusch Nov 21 '13 at 17:01

score 5 · Answer 3 · answered May 21 '09 at 17:02

5

I am pretty sure it is possible- we use TOAD for Oracle, and it lets you cancel long-running queries, as described here. I'm not sure how they do it though.

answered May 21 '09 at 17:02

JosephStyons

57,317
63
160
234

score 3 · Answer 4 · answered May 20 '09 at 17:09

You could have the background worker fire off the actual database call on a different thread, and then periodically check to see if either the database call has finished, or cancel has been pressed, at which point you could kill off the database thread. This wouldn't actually help the database load any (as your query has been sent and is still processing) but it does release your local resources related to it.

score 3 · Answer 5 · answered May 20 '09 at 19:45

3

I think the best solution seems to kill sessions via monitoring table.

With Oracle you can make it as says Burnsys

In Firebird 2.5 it will looks the same

I hope something similar exist in Ms SQL

answered May 20 '09 at 19:45

Hugues Van Landeghem

6,755
3
34
59

score 2 · Answer 6 · answered May 20 '09 at 17:17

2

If you're using an SQLCommand, you could try calling it's Cancel method.

answered May 20 '09 at 17:17

stuartd

70,509
14
132
163

Burnsys · Answer 7 · 2009-05-21T16:57:13.240

2

What about opening a new connection to the database, login in as sysdba and sending a "ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE" command specifying the SID of the process you want to terminate.

To get the sessionID: select sid from v$mystat where rownum = 1

To get Serial#: select sid, serial# from v$session where sid = :SID

http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

EDIT: WW idea for not Login as sysdba here: http://forums.oracle.com/forums/thread.jspa?threadID=620578

edited May 21 '09 at 16:57

answered May 20 '09 at 17:19

Burnsys

854
1
8
11

3

Having your application login as sysdba is not a good idea from a security standpoint. I would suggest wrapping this kill command in a PL/SQL package that can only kill certain sessions. – WW. May 21 '09 at 06:03
True, in any case, you may need some special privileges to run the SQL Package, but also you have to establish a new connection to send the kill command, so you will log to the base with special privileges only to terminate the session, the rest of the application can use the same user it did before. – Burnsys May 21 '09 at 13:01

score 1 · Answer 8 · answered May 23 '12 at 09:16

I have tried both Cancel and Close with ADO 2.8 and SQLOLEDB or SQL Server native client. With Cancel, the recordset stops fetching data, but in the backround the reading from the server continues and consumes memory from the application. In a 32 bit application it can happen that you get an "out of memory" message some minutes later. When I close the recordset (or the connection, with or without Cancel before), ADO 2.8 waits until all records are fetched.

I don't know if ADO.NET does it better, but I think it's a good idea to monitor memory and network access after Cancel/Close to be sure that ADO really stops reading data.

score 1 · Answer 9 · answered Sep 11 '18 at 16:06

KILL SESSION was the only working way for me to cancel the long running query. I am using the managed oracle provided and OracleCommand.Cancel() works some times but usually it's not working. Also OracleCommand.CommandTimeout is not respected according to my tests. Some time ago when i was using the unmanaged oracle provided i managed to cancel commands but not any more with the managed one. Any way killing the session was the only option. The query is not running on the UI thread but on a seperate thread. The cancel command is send from the UI thread. Its a little more complex because the application uses a middletier using WCF but at the end of the day i am killing the session. Of cource when running the query i have to find and save the session in order to kill it if necessary. There are many ways to find sid and serial# in order to kill the oracle session and i want waste your time explaining something you already know.

score 0 · Answer 10 · answered Jul 27 '19 at 06:42

Oracle introduced ALTER SYSTEM CANCEL SQL in 18c. You would need to add some kind of comment with UID to your SQL and then look for it something like this

SELECT S.SID||','||S.SERIAL#
                    FROM GV$SESSION S, V$SQL Q
                    WHERE S.USERNAME IS NOT NULL
                    AND S.STATUS = 'ACTIVE'
                    AND S.SQL_ID IS NOT NULL
                    AND Q.SQL_ID = S.SQL_ID
                    and sql_text like '%{queryId}%'

And then run another operation from .NET ALTER SYSTEM CANCEL SQL 'SID, SERIAL'

score -2 · Accepted Answer · answered May 20 '09 at 17:06

-2

I dont think it is possible. Here is a link to a discussion on Oracle's website about this topic: http://forums.oracle.com/forums/thread.jspa?threadID=400492&start=15&tstart=0

answered May 20 '09 at 17:06

Josh Curren

10,171
17
62
73

1

This is flatly wrong - see my comment below. It definitely is *possible*, because I have seen it done. I wish I knew how, though... – JosephStyons May 26 '09 at 19:18
I wish original author of this thread unmark this answer as accepted if as what JosephStyons says it is possible. – swcraft Oct 22 '18 at 21:12

How to cancel a long-running Database operation?

11 Answers11

Linked