using TransactionScope : System.Transactions.TransactionAbortedException: The transaction has aborted

Question

We're trying to do indirect nesting transaction using the code below, .NET 3.5 ,& SQL Server 2005.

MSDN says that when using TransactionScope, a transaction is escalated whenever application opens a second connection (even to the same database) within the Transaction.

void RootMethod()
{
   using(TransactionScope scope = new TransactionScope())
   {
      /* Perform transactional work here */
      FirstMethod();
      SecondMethod();
      scope.Complete();
   }
 }

void FirstMethod()
{
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
   {
     using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")) 
       {
     string insertString = @"
             insert into Categories
             (CategoryName, Description)
             values ('Laptop1', 'Model001')";
         conn1.Open();
         SqlCommand cmd = new SqlCommand(insertString, conn1);
         cmd.ExecuteNonQuery();
        }
      scope.Complete();
    }
 }

 void SecondMethod()
 {
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
   {
       using (SqlConnection conn2 = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")) 
       {
     string insertString = @"
             insert into Categories
             (CategoryName, Description)
             values ('Laptop2', 'Model002')";

         conn2.Open();  //Looks like transactionabortedException is happening here
         SqlCommand cmd = new SqlCommand(insertString, conn2);
         cmd.ExecuteNonQuery();
        }
        scope.Complete();
    }
  }

Occasionally, the transaction fails that, is not promoting to DTC, and we are getting the following as the inner stack trace,

System.Transactions.TransactionAbortedException: The transaction has aborted. ---> 
System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> 
System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.     
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)     
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()     --- End of inner exception stack trace ---     
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()     
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)     
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)     
--- End of inner exception stack trace ---     
at System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx)     
at System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)     
at System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)     
at System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)     
at System.Transactions.TransactionScope.PushScope()     
at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption)    

Can anyone please help me figuring out the reason for this failure?

Ian

Have you tried closing the connection ? Sometimes I do get such errors, when the connection is already open. May be I could be wrong. – Sabbat

have you tried adding ;MultipleActiveResultSets=True in the connection string – Beatific

Answer 1

If you use TransactionScope and you:

• open more than one connection to a database and
• are connecting to a SQL Server 2005 server

the transaction will be escalated to DTC. Check this other SO question: TransactionScope automatically escalating to MSDTC on some machines?

The solution is either:

• Use SQL Server 2008 or

• Use SqlTransaction instead of TransactionScope just like the former answer suggests:

  using (var conn = new SqlConnection(connectionString))
  {  
      using (var tx = conn.BeginTransaction())
      {
          FirstMethod(conn);
          SecondMethod(conn);
          tx.Commit();
      }
  }
  

Comfort

Answer 2

i can propose to you a better way to achieve your goal. there should be a single transaction for 2 DB call per connection.

it should be like

using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"))
{ 
    using (conn1.BeginTransaction()
    {
        try
        {
            FirstMethod(Conn1);
            SecondMethod(Conn2);
        }
        catch()
        {
        }
    }
}

Interventionist

You can't say this is better solution. What if you can't change methods to accept connection? What if the methods are in different objects? What if those methods already have too many parameters? And this is ugly too. – Leeleeann