Hopefully you are familiar with the greatness of the TransactionScope class in .NET.  It provides implicit ambient transaction handling, which can greatly simplify transaction handling in your code.

But this ease of use comes with a significant caveat.  The TransactionScope’s default constructor is, for the purposes of SQL Sever database programming, broken.  TransactionScope’s default constructor defaults the isolation level to Serializable and the timeout to 1 minute.  IMO both these settings are harmful when working against SQL Server.

The transaction timeout is bad because it’s obscure.  A SqlCommand already has a CommandTimeout property that defaults to 30 seconds.  If you explicitly extend the CommandTimeout on a SqlCommand, it’s probably unexpected that your transaction would timeout before that.  But at least the timeout default can be changed in your application configuration file.

But the choice of Serializable as the default isolation level much worse.  In SQL Server SERIALIZABLE transactions are rarely useful and extremely deadlock-prone.  Put another way, when the default READ COMMITTED isolation level does not provide the right isolation semantics, SERIALIZABLE is rarely any better and often introduces severe blocking and deadlocking problems.  And since the TransactionScope is the recommended way to manage transactions in .NET, its default constructor is setting up SQL Server applications to be deadlock-prone.  In fact I was prompted to write this post after working with some customers who were getting deadlocks in their applciation, and who had no idea that they were running transactions under the SERIALIZABLE isolation level.

So please, copy this C# code:

public class TransactionUtils {
  public static TransactionScope CreateTransactionScope()
  {
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
  }
}

or this VB.NET code:

Imports System.Transactions

Class TransactionUtils Public Shared Function CreateTransactionScope() As TransactionScope Dim transactionOptions = New TransactionOptions()
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted
    transactionOptions.Timeout = TransactionManager.MaximumTimeout
    Return New TransactionScope(TransactionScopeOption.Required, transactionOptions)

  End Function End Class

And use TransactionScope for SQL Server, just not new TransactionScope().

David

dbrowne_at_microsoft