1300 894 506

Managing Exceptions & SQL Transactions

A better try…catch…finally pattern

A very common approach to managing exceptions that occur while a database transaction is in progress is to simply wrap it in a try catch block and if an exception occurs call Rollback on the transaction. This approach would typically appear like the example below:

using (SqlConnection conn = new SqlConnection())
{
    conn.Open();
    using (var tran = conn.BeginTransaction())
    {
        try
        {
            DoOperation();//Exception may occur
            tran.Commit();
        }
        catch
        {
            tran.Rollback();
            throw;
        }
    }
}

Appendix 1

One of the issues with this approach is you are catching an exception that you are not handling. The exception handler doesn’t alter the execution of the program at all it just rolls back the transaction then throws the exception again to either be handled or not somewhere up the execution stack.

The second issue is a bit more obscure but I have run into it on more than a few occasions. Lets alter our example a little bit by adding in our own bit of validation that, when failed, throws our own exception.

using (SqlConnection conn = new SqlConnection())
{
    conn.Open();
    using (var tran = conn.BeginTransaction())
    {
        try
        {
            bool result = DoOperation();//Exception may occur
            if (!result)
                throw new Exception("The operation failed!");

            tran.Commit();
        }
        catch
        {
            tran.Rollback();
            throw;
        }
    }
}

Appendix 2

This executes much as you might expect. If the result is false the exception is thrown, caught, the transaction is rolled back and the exception is rethrown. However, there is one problem. In the stack the line number reported for the source of the exception is the line of our “throw;” statement. Not the “throw new Exception(“The operation failed!”);” statement. This oddity only occurs if the exception is thrown from within our try block on the same frame of the stack (meaning it wasn’t thrown from a method called from within our try block, but directly from a statement inside the try block itself).

When I first encountered this I believed it to be a bug in the .Net runtime. Here is what Mike Magruder (who was introduced to me as the “architect in charge of the CLR’s exception handling system.”) had to say about it.

This is by design. This has been odd ever since v1. We have a stack trace here, with one entry per stack frame, and we have a choice about what line to show for a method with a catch/rethrow. We could show the original throw point in the method, or we could show the rethrow point. Both can make sense, and you might be happier with one or the other depending on the scenario.

I can’t honestly say why they chose that one, but I could speculate, and that speculation would start with the observation that a “catch { work(); throw;}” is often better represented by “finally {work();}”, and perhaps they believed that they would help more people by marking the point of rethrow as those might be more complicated scenarios.

I’ve always regarded “finally” as a somewhat useless feature so this was a bit of a revelation for me. I wondered for a while how my finally block could know if an exception occurred or not. The solution as it turns out was very simple.

using (SqlConnection conn = new SqlConnection())
{
    conn.Open();
    bool fail = true;

    using (var tran = conn.BeginTransaction())
    {
        try
        {
            bool result = DoOperation();//Exception may occur
            if (!result)
                throw new Exception("The operation failed!");

            tran.Commit();
            fail = false;
        }
        finally{
            if (fail)
                tran.Rollback();
        }
    }
}

Appendix 3

Like I said, it’s very simple. Just include a flag variable that indicates if the code has executed successfully. The caveat is you do end up having this additional variable, you do have to manually set it and you do have to test its value. However, that is a small price to pay for optimal code (that doesn’t produce odd/wrong stack traces). After working with this approach for a month or so ,(my actual implementation look s a bit different as I don’t deal with ADO.Net classes directly) I have grown to like it.

One colleague asked me what to do if I, as a developer, wanted to log the exception. I think in this case if you want to catch and log the exception and display a nice error message to user as part of the normal page operation you could do that (it would/should probably happen further down the stack). Otherwise, you could show the user an error page using the inbuilt mechanisms of ASP.Net and also bind to the Application Error event which will fire for any unhandled exception, that would be a good place to log the exception and you are free of any catch/throw concerns at the point.