Issue with System.Transactions, SqlConnection and Timeout

Just recently, a post was made in Microsoft Forums regarding a bug/behavior of Committable Transactions and SqlConnection timeout. The same issue is evident for TransactionScope which was posted in 2006. Good thing there is a fix.

I have used TransactionScope a number of times including in one of my previous posts on Unit Testing (integration testing if you're particular about it) DataAccess so will focus on it for now (but as said, same issue with Committable Transaction class).

When you use TransactionScope and set the timeout to a certain value (not sure what is the default is not specified) and the timeout elapsed before the TX is completed, what happens is that actions made before the timeout is rolled back but after that, the connection unbinds itself from the transaction and if any action, places itself in autocommit mode (just like a regular connection) and if actions are made after the timeout (BUT still inside the TransactionScope, since these were performed in autocommit mode, they will not be rolled back).

Trying out the code below (or the downloadable sample project at the bottom) you will notice that in the "BASIC" example (which demonstrates the issue), you try to insert 5 rows, TransactionScope timeout more or less happens after the 2nd row, then after the TransactionScope, you check the database and there are 3 rows committed (instead of NONE). 

The good news is that MSFT found this issue too and had a fix for it a little after the release (probably after the System.Transaction or release of .NET 2.0) which involves a new keyword in the connection string : transaction binding defined in MSDN as :

Controls connection association with an enlisted System.Transactions transaction.

Possible values are:

Transaction Binding=Implicit Unbind;

Transaction Binding=Explicit Unbind;

Implicit Unbind causes the connection to detach from the transaction when it ends. After detaching, additional requests on the connection are performed in autocommit mode. The System.Transactions.Transaction.Current property is not checked when executing requests while the transaction is active. After the transaction has ended, additional requests are performed in autocommit mode.

Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called. An InvalidOperationException is thrown if Transaction.Current is not the enlisted transaction or if the enlisted transaction is not active.

So as you can see, the Implicit Unbind was the default behavior (which exibits the issue when the TX times out) and using explicit unbind will have the connection remain "bound" to the transaction instead of detaching itself and live it's life on it's own (and in on autocommit mode). Effectively, those actions performed after the Transaction Scope times out will also be uncommitted and we have a consistent behavior.

Here's my code while trying to verify the issue on my own (code taken from the forums with a few additions to help illustrate better). NOTE: Don't forget to modify the connection accordingly (as you see fit on your environment). Had this on VS2005, .NET 2.0 and the sample project is a console application.

    1 using System;

    2 using System.Data.SqlClient;

    3 using System.Transactions;

    4 using System.Threading;


    6 class Demo

    7 {

    8     static string connectionString;


   10     static void Main(string[] argv)

   11     {

   12         // ** Change connection strings accordingly


   14         Console.WriteLine("TransactionScope - BASIC");

   15         Console.WriteLine("============================");

   16         connectionString = @"server=.\sql2005;database=testDB;integrated security=SSPI";

   17         TransactionScopeTest();


   19         Console.WriteLine();

   20         Console.WriteLine("TransactionScope - IMPLICIT UNBIND");

   21         Console.WriteLine("============================");

   22         connectionString = @"server=.\sql2005;database=testDB;integrated security=SSPI;transaction binding=implicit Unbind;";

   23         TransactionScopeTest();


   25         Console.WriteLine();

   26         Console.WriteLine("TransactionScope - EXPLICIT UNBIND");

   27         Console.WriteLine("============================");

   28         connectionString = @"server=.\sql2005;database=testDB;integrated security=SSPI;transaction binding=explicit Unbind;";

   29         TransactionScopeTest();


   31         Console.ReadKey();

   32     }


   34     private static void TransactionScopeTest()

   35     {

   36         try

   37         {

   38             ReCreateTable();

   39             Console.WriteLine("Table recreated");


   41             try

   42             {

   43                 using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.FromSeconds(2)))

   44                 {

   45                     Console.WriteLine("Transaction started lasting 2 seconds");

   46                     using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

   47                     {

   48                         con.Open();

   49                         Console.WriteLine("Server is {0}", con.ServerVersion);

   50                         Console.WriteLine("Clr is {0}", Environment.Version);


   52                         for (int i = 0; i < 5; i++)

   53                         {

   54                             using (SqlCommand cmd = con.CreateCommand())

   55                             {

   56                                 cmd.CommandText = "insert into TXTEST values ( " + i + " )";

   57                                 cmd.ExecuteNonQuery();

   58                                 Console.WriteLine("Row inserted");

   59                             }


   61                             Thread.Sleep(TimeSpan.FromSeconds(1));

   62                         }


   64                         Console.WriteLine("Committing... now we get the timeout (sort of)");


   66                         tx.Complete();

   67                     }

   68                 }

   69             }


   71             catch (Exception e)

   72             {

   73                 Console.WriteLine(e.Message);

   74             }


   76             Console.WriteLine("Table contains {0} rows!!!", CountTable());

   77             DropTable();

   78         }


   80         catch (Exception e)

   81         {

   82             Console.WriteLine("Unexpected error:");

   83             Console.WriteLine(e.ToString());

   84         }

   85     }


   87     static void ReCreateTable()

   88     {

   89         try

   90         {

   91             DropTable();

   92         }


   94         catch (Exception) { }


   96         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

   97         {

   98             con.Open();

   99             using (SqlCommand cmd = new SqlCommand("create table TXTEST ( F1 int )", con))

  100                 cmd.ExecuteNonQuery();

  101         }

  102     }


  104     static int CountTable()

  105     {

  106         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

  107         {

  108             con.Open();

  109             using (SqlCommand cmd = new SqlCommand("select count(*) from TXTEST", con))

  110                 return (int)cmd.ExecuteScalar();

  111         }

  112     }


  114     static void DropTable()

  115     {

  116         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

  117         {

  118             con.Open();

  119             using (SqlCommand cmd = new SqlCommand("drop table TXTEST", con))

  120                 cmd.ExecuteNonQuery();

  121         }

  122     }

  123 }

Download Demo - (5.05 kb)

The sample project also contains another class for illustrating the issue with Committable Transaction. Just exclude the TransactionScope class and uncomment the Main method in CommittableTransactionDemo.cs. 

Please feel free to drop me message if I'm missing something or if there's any issues with the download. :D

More Information:

MSDN - Implementing an Implicit Transaction with TransactionScope 

MSDN - ConnectionString


  • Hi. I have inherited a messy applicaton that uses SQL Connections both for activity that is NOT in a Transaction, and for activity that IS in a transaction.

    So, if I put "Explicit Unbinding" in my ConnectionString, and a Transaction Times-out, so the COnnection is put back into the Pool and it is still enlisted to the TimedOut Transaction. Now that Connection is pulled out of the pool and used in code that does not enlist it into a transaction, that code will fail with a InvalidOperationException.

    The MSDN article on SQLConnection ConnectionString property says "Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called".

    I assume then the connection put back in the pool and later used by non-transactional code will never have been Closed() nor have TransactionEnlist() inv oked on it.

    What do you think? I would need to be very careful to use 2 separate Connection Pools, one with Explict Unbind used by my transactional code, and one Implicit Unbind used by non-transaction code.

  • The behaviour that the original poster described has been changed in .Net 4.0. Now the connection will only auto-detach from the transaction when the TransactionScope is disposed. So if the transaction is aborted then any command on that connection will throw an InvalidOperationException instead of being autocommitted. In other words, in .Net 4.0 it is not necessary to use Explicit Unbind.

  • This is maddening. Like your hands tied behind your back, and was repeatedly punching your stomach. No matter how hard you try, you can not be turned away, nothing can be done to stop it. You just need to be patient bully tired, stop picking on you. You hope that every day, others pick their own size. This is the life of the Cleveland Browns fan.

  • As someone who pays a large amount of concentration on this
    type of stuff, I can verify this page is completely right.

  • I'm truly enjoying the design and layout of your website. It's a very easy on the eyes which makes it much more enjoyable for me to
    come here and visit more often. Did you hire out a designer to create your theme?
    Fantastic work!

  • With havin so much written content do you ever run into any issues of plagorism or copyright violation?
    My blog has a lot of exclusive content I've either written myself or outsourced but it looks like a lot of it is popping it up all over the internet without my authorization. Do you know any techniques to help stop content from being ripped off? I'd definitely appreciate it.

Comments have been disabled for this content.