using new TransactionScope() Considered Harmful

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

Comments

• Anonymous
  December 21, 2010
  Thanks! This REALLY helped me fix a bug.

• Anonymous
  March 01, 2011
  Default behavior is odd, but the option to changes it exists, which is awesome.

• Anonymous
  January 18, 2012
  Nice post David, was looking to see what the deal was with TransactionScope's isolation levels, and this explained it and gave good advice all in one. thanks

• Anonymous
  June 25, 2013
  Thanks, this has just clarified a bunch of deadlocks in the system we have just migrated from Oracle to SQL Server. :-)

• Anonymous
  December 01, 2013
  What is the value of TransactionManager.MaximumTimeout and why did you choose to set it explicitly?

• Anonymous
  January 06, 2014
  @One thing i didn't understand... If you read the article, the author mentions that by default the TransactionScope has a 1 minute timeout.  So if all of your commands under the scope exceed one minute, they will all be rolled back.  This is in addition to, and supersedes, the CommandTimeout you might set on each command. That's why he set it to max. -LS3

• Anonymous
  February 10, 2014
  The comment has been removed

• Anonymous
  August 17, 2014
  Thanks for this. Solved a major problem.

• Anonymous
  November 08, 2014
  Thanks David

• Anonymous
  December 16, 2014
  @Larry, The difference is when you do new TransactionScope(), it uses default properties/settings that cause problems (refer to post). But, you should still use a transaction scope, so you need to manually create the transaction scope object like he did in the code provided.  He uses properties that avoid these problems.

• Anonymous
  April 15, 2015
  Thanks David! It explained why I encountered so many deadlocks when dealing with SQL servers. This may resolve the critical problems! I'll consider to use your suggestions!

• Anonymous
  June 02, 2015
  Thanks...this is still helpful...hope it would be added to the library

• Anonymous
  September 23, 2015
  use System.Transactions.IsolationLevel.ReadCommitted var transactionOptions = new TransactionOptions(); transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; transactionOptions.Timeout = TransactionManager.MaximumTimeout;

• Anonymous
  March 16, 2016
  Thanks David.