First published on MSDN on Dec 15, 2009
I was recently developing a set of SQLCLR functions and procedures for an internal project. One of the tests I added to my suite was to cancel the query (attention) and make sure I handled it properly in my .NET implementation. What I found was much more than I expected and it is something that every SQLCLR developer should understand.
When a query is cancelled that is currently executing in SQLCLR an System.Threading.ThreadAbortException exception is raised. It seems harmless enough to add a catch or finally block to your code to cleanup necessary resources. What I found is that it is not just as easy as catching the ThreadAbort. In some scenarios the ThreadAbort can be upgraded to a rude abort and the catch/finally block(s) are not executed.
Let's start with a simple scenario.
MyFunction
{
MyObj ob = new MyObj();
… do some work …
}
The safe assembly allocates MyObj and it does get cleaned up until the garbage collection process executes the associated finalizer(s). This is standard CLR behavior. If this same code encounters a ThreadAbort exception it will exit without completing the function and the same finalizer cleanup applies.
What if you have an object that you want to force disposal before you exit the function.
MyFunction
{
try
{
MyObj ob = new MyObj();
… do some work …
}
finally
{
… do special cleanup work here - Perhaps you want to make sure a file, connection or other object is disposed/closed quickly …
}
}
The code above works fine as long as you are running a Safe SQLCLR procedure. The finally block is executed and you are allowed to cleanup from the abort.
CAUTION: Make sure the logic in a catch(ThreadAbortException) or finally block is short as the user has asked to 'cancel' execution and no longer wants to wait.
In my case I was executing APIs in an external DLL so the CLR was registered as UNSAFE and the API returned UNMANGED handles that needed to be cleaned up. Here is where it got a bit confusing.
If your CLR function or procedure is executing UNMANGED code the ThreadAbortException is not guaranteed to trigger your catch or finally block. The default behavior of the ThreadAbortException is to allow approximately 500ms to respond to the the exception or it is upgraded to the rude abort. The rude abort may not fire the ThreadAbortException catch block or the finally block.
In my case this would leak a HANDLE from API and since the handle was not closed a Mutex was held so subsequent callers to my SQLCLR procedure would hang.
To fix this you must use a
SafeHandle
to store the
UNMANGED HANDLE
or other object you want to make sure is destroyed, using the SafeHandle critical finalizer. Because my initial implementation did not use a SafeHandle there was no object for CLR to finalize and I would leak the HANDLE.
You can find examples using the SafeHandle at the following blogs.
http://blogs.msdn.com/shawnfa/archive/2004/08/12/213808.aspx
http://blogs.msdn.com/cbrumme/archive/2004/02/20/77460.aspx
You could also consider using RuntimeHelpers..::.ExecuteCodeWithGuaranteedCleanup but to me that was much more work than using the SafeHandle.
=======================================
Tricky but Stupid - Don't do this.
I should probably not call myself stupid but my first attempt to get around this was not to use the SafeHandle. What I did was create a second thread that did all the API work. Since the ThreadAbortException occurs on the main worker I could avoid it on the second thread. I only used managed objects on the main worker so I could catch the ThreadAbortException and I would interrupt the second thread. My second thread could then catch the ThreadInterruptException and cleanup as expected.
The problem with this is that each CLR request requires 2 worker threads from the SQL Server process and will exhaust the worker thread pool.
=======================================
There is a very good post discussing CLR and the ThreadAbortException:
http://blogs.msdn.com/clrteam/archive/2009/04/28/threadabortexception.aspx
WARNING: For SQLCLR do not attempt to clear the Abort status. The intent of the end user was to cancel so be sure to honor that request.
Bob Dorr - Principal SQL Server Escalation Engineer
