Using TransactionScope: System.Transactions.TransactionAbortedException: Transaction Aborted

Question

We are trying to execute an indirect investment transaction using the following code: .NET 3.5, & SQL Server 2005.

MSDN reports that when using TransactionScope, a transaction escalates whenever the application opens a second connection (even in the same database) in 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(); } } 

Sometimes a transaction fails, which is not conducive to DTC, and we get the following as an internal 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 someone help me figure out the reason for this failure?

Answer 1

If you use TransactionScope and you:

• open multiple database connections and
• connect to SQL Server 2005

transaction will be increased to DTC. Check out this other SO question: Does TransactionScope automatically migrate to MSDTC on some machines?

Solution: either

• Use SQL Server 2008 or

• Use SqlTransaction instead of TransactionScope, as in the previous answer:

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

Answer 2

I can offer you the best way to achieve my goal. There must be one transaction for two database calls per connection.

he 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() { } } }