Lightweight Promotable Transactions, SqlDataReader and Commitment.

Here's a small issue you may need to watch out for when using a System.Transactions transaction with SqlClient v2.0 and Sql Server 2005.  This combination is required to get LWPTs.

The basics of LWPTs have been pretty widely discussed (here's an example: https://blogs.msdn.com/angelsb/archive/2004/07/12/181385.aspx), so I won't go into it too much.  The tricky part I'm looking at is when the transaction ends.  Let's look at an example:

  SqlConnection conn = new SqlConnection(someConnectionString);
  try
  {
    using (TransactionScope scope = new TransactionScope()) {
    {
      conn.Open();
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT 1; SELECT 2";
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
      }
    } // end of transaction scope
  }
  finally
  {
    if (conn != null)
    conn.Close();
  }

Run this, and you get an error that the connection is busy.  What?!?

At the end of the TransactionScope's using statement, the scope is Disposed(), which in this case causes a rollback (we didn't call scope.Completed()). Since it's a LWPT, the underlying transaction is just a Sql Server local transaction on the connection, and the rollback has to be issued across the connection.  The problem is that the reader is still open on the connection (notice the second select statement in the command text), so the attempted rollback fails.  This happens any time you let a reader's lifetime run into the transaction scope ending.

There are a couple of ways around the connection busy issue, but they have their own potential problems.  One is to switch the transaction to a distributed transaction.  Distributed transactions communicate through the transaction coordinator to commit or roll back, not directly over the database connection.  Of course the transaction may not commit while the reader is open, since the batch the server is running to support the reader may still be working in the database.

A second way around the problem is to turn MARS on, which will allow the commit / rollback code to execute a second batch on the connection.  This approach still suffers from the problem of the batch the reader is on hogging the the transaction context, and the commit / rollback may fail.

The moral?  The best practice is to always close your readers INSIDE the transaction scope they are associated with.  This is another good reason to wrap readers in a "using" statement.  The language itself will prevent you from having overlapping lifetimes between the scope's "using" and the reader's "using".

~Alazel

Updated on 4/15 to add DTC and MARS workarounds.