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 (14)

  1. Sam says:

    Thanks! This REALLY helped me fix a bug.

  2. Seann Alexander says:

    Default behavior is odd, but the option to changes it exists, which is awesome.

  3. hoges says:

    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

  4. Roy Cornelissen says:

    Thanks, this has just clarified a bunch of deadlocks in the system we have just migrated from Oracle to SQL Server. 🙂

  5. One thing i didn't understand... says:

    What is the value of TransactionManager.MaximumTimeout and why did you choose to set it explicitly?

  6. Timeout says:

    @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

  7. Larry says:

    Last line mentions the following:

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

    Can you please tell me the difference in the script for TransactionScope for SQL Server and just new TransactionScope()?

    i am unable to find the difference in script and thus cannot determine if my script is using Transaction scope for SQL Server or just new TransactionScope().

    Thanks!!

  8. Steve Altman says:

    Thanks for this. Solved a major problem.

  9. Thomas says:

    Thanks David

  10. Jar Jar Binks says:

    @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.

  11. IEBasara says:

    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!

  12. Anonymous says:

    Thanks…this is still helpful…hope it would be added to the library

  13. uday says:

    use System.Transactions.IsolationLevel.ReadCommitted

    var transactionOptions = new TransactionOptions();

    transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

    transactionOptions.Timeout = TransactionManager.MaximumTimeout;