Killing/Aborting a thread with a single, giant task (e.g. SQL Query)

Question

Okay,

There seems to be a lot of discussion on here about how terribly awful Thread.Abort() is, and how to work around using Thread.Abort() in your code by adding exit checks in your loop.

This assumes that you control how the work is being partitioned in a way that's granular enough to loop over it.

But how do I do the following:

1. Launch worker thread to do something while keeping the UI responsive. For all intents and purposes the code for the worker thread looks like this:

   public override void Run()
   {
       try
       {
           _dataTable = ExecuteSingleGinormousSqlQuery();
       } finally
       {
           // close the connection if it was left open.
       }
   }
   

2. Have "Cancel" button so that the user can interrupt this if they get bored of waiting.

It doesn't seem like there's any other way around this than with Thread.Abort(). Is that true? Furthermore, in empirical tests, it doesn't even seem like Thread.Abort() actually kills the thread until the call that is making the query, e.g.

    new SqlDataAdapter(cmd).Fill( ds );

comes back in the first place, thus significantly negating its utility.

Is there a way around this? Best practices?

Answer 1

You're the engineer. No one will throw you in jail for using Abort ().

All dangerous and/or deprecated constructs have specific circumstances where they may be desirable and sometimes necessary. This may be one such case. Mastery of any skill is knowing the rules and knowing when to break them.

But... and it's a big but... the big question is: what happens when `ExecuteSingleGinormousSqlQuery()' itself is aborted? Will it leave the client, the server or the database in a screwed up state? Are you sure you know what will happen? Do you have access to all the source code? And even if you do, have you gone over every line to make sure it's safe?

The problem with Thread.Abort () is that aborts can happen anywhere, and we know there have been bugs in the CLR that can make seemingly safe code misbehave.

Answer 2

You're right that it won't immediately abort a thread that's busy in an interop call, which is probably why it doesn't stop until after the database call is completed. (See http://www.devx.com/codemag/Article/17442/0/page/4)

I suppose you could make the call asynchronous (see http://www.devx.com/dotnet/Article/26747) but all that means is that you don't have to abort the thread. The call continues on in the background and isn't being aborted on the database end. You might as well just use a synchronous call and let the thread complete, ignoring its results. The other issue is that you can't use a simple Fill method to do the work, if you want to go async.

Answer 3

I would kill the query or the connection to the database. It can be done on most servers via simple command or procedure call.

You can easily kill query in mysql issuing the following command:

KILL QUERY <query_id>

or in mssql

KILL <unit of work id>

Answer 4

I agree with Larry. As long as you're pretty sure that the SQL query won't screw up the database and it doesn't look it will cause problems elsewhere in your app by being killed off, that might be an "acceptable" use for Abort.

However, remember with this also that even if you kill the SQL, if it's one that makes updates or deletes, it will still take a while to rollback the changes and will not free up your database tables immediately on hitting cancel. Also, you might be better served to focus on optimizing the database instead by optimizing the query, adding indexes, etc, to make the Ginormous query not so long running as it apparently is.

Answer 5

The SqlCommand object has a Cancel() method which you can call from another thread to attempt to cancel a running SQL query. It's not guaranteed to succeed, and will depend on the database, drivers and the actual query running, but you might find it's ok for what you need.

Answer 6

Another option is instead of executing your long running operation in a background thread, you execute it in a new process.

There are 2 downsides:

1. A process has a performance penalty that is several orders of magnitude greater than that of a thread. Both in startup time and in memory overhead. In your case of a user interface scenario however, the startup time of a new background process should be negligible and can be measured as happening "in the blink of an eye."

2. You are required to use some form of interprocess communication method to get the results back to your main program, which is usually more complicated than sharing memory between threads.

The benefit of a process vs. thread is that the semantics of killing a process are well understood, and you are pretty much guaranteed that the process will immediately terminate and resources will not be leaked.

Peace

Answer 7

If you do not mind refactoring your code you could use the SqlCommand.BeginExecuteReader method or one of the other asynchronous variants.