TransactionScope and SqlServer

Well, here it is, almost the end of the month, and as usual I’m behind in my blog posting. It seems that I’m lucky to get one post a month this past year. But this month, being almost the end of December, I should at least wish all my readers “Happy Holidays”.

Now, on to today’s topic. This may be old news; after all, TransactionScope has been around since 2.0, but I hadn’t been using it back then. I’ve just really started utilizing it during the past year and recently discovered some interesting gotchas.

First, let’s start with a little snippet of code:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        // transactional stuff in here, perhaps calls to SQL Server

        ...

        scope.Complete();
    }
    catch (Exeception ex)
    {
        // do whatever you need to here, we will NOT complete the transaction
        Console.WriteLine("*** ERROR " + ex.Message);
    }
}

Now, this is all well and good … or so you think. But, there is one little gotcha here …. and that is that when TransactionScope is instantiated without any parameters, the default for its IsolationLevel is Serialized … the most restrictive that there is. On the other hand, for Transactions in SQL Server, the default IsolationLevel is ReadCommitted and, for the most part, that is what you typically will want to use.

In my case, I’m doing a lot of writing to the database, in lots of different threads. If I don’t mess with the TransactionScope, then IsolationLevel.Serialized will severely slow things down, because the database tables, in effect, get locked … because Serializable prevents new data from being added. Wow, not really what I intended!

Well, OK, so I guess we can change the above snippet to this:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted}))
{    
    try    
    {        
        // transactional stuff in here, perhaps calls to SQL Server 
              
        ...        

        scope.Complete();    
    }    
    catch (Exeception ex)    
    {        
        // do whatever you need to here, we will NOT complete the transaction        
        Console.WriteLine("*** ERROR " + ex.Message);    
    }
}

Wow, that’s messy, but it’s necessary.

But wait, there’s more!!  If your current transaction is operating under another ambient transaction (“ambient” is like a parent transaction … I don’t know why they didn’t use the same terminology), the IsolationLevel has to be the same! An exception is thrown if it’s not! So, you’ve got to remember to use this same syntax every time you instantiate a TransactionScope! Yuck!

But, fortunately, there’s a better idea. It’s much easier and cleaner to use a Utility class with static methods for this sort of thing:

public class Utils
{
    /// <summary>
    /// The reason for this method to return a TransactionScope is because the default IsolationLevel 
    /// when not specified is Serializable, which is the most restrictive level and will cause all kinds
    /// of deadlock problems with Sql Server!!
    /// 
    /// We can add more overloads later if we want more options.
    /// The TransactionScoope that gets returned here is Required and IsolationLevel.ReadCommitted.
    /// </summary>
    /// <returns></returns>
    public static TransactionScope GetTransactionScope()
    {
        return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted});
    }
}

And now, we make sure that all our developers know to always use this method when creating a TransactionScope, like this:

using (TransactionScope scope = Utils.GetTransactionScope())
{    
    try    
    {        
        // transactional stuff in here, perhaps calls to SQL Server 
              
        ...        

        scope.Complete();    
    }    
    catch (Exeception ex)    
    {        
        // do whatever you need to here, we will NOT complete the transaction        
        Console.WriteLine("*** ERROR " + ex.Message);    
    }
}

Happy Coding and Happy Holidays!