Share via

using new TransactionScope() Considered Harmful

  • Article

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.