使用TransactionScope：System.Transactions.TransactionAbortedException：事务已中止

Question

我们正在尝试使用以下代码进行间接嵌套事务，使用的是.NET 3.5和SQL Server 2005。

MSDN指出，当使用TransactionScope时，在事务内打开第二个连接（即使是连接到同一个数据库）时，事务会升级。

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();
    }
  }

有时候事务会失败，也就是没有升级到DTC，我们会得到以下内部堆栈跟踪：

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)    

请问有人能够帮我找出这个失败的原因吗？

- Pete Brook

---

你试过关闭连接吗？有时候当连接已经打开时，我也会遇到这样的错误。也许我错了。 - The King

你尝试在连接字符串中添加 ;MultipleActiveResultSets=True 了吗？ - Anand M Arora

Answer 1

如果您使用TransactionScope并且：

• 打开多个连接到数据库，并且
• 连接到SQL Server 2005服务器

事务将升级为DTC。请查看此其他SO问题：TransactionScope在某些计算机上自动升级为MSDTC？

解决方案为：

• 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();
      }
  }
  

- Rodolfo Grave

Answer 2

我可以向您提出更好的实现目标的方式。每个连接应该有2个数据库调用的单个事务。

它应该像这样。

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

- ratneshsinghparihar

---

你不能说这是更好的解决方案。如果你无法更改方法来接受连接怎么办？如果这些方法在不同的对象中怎么办？如果这些方法已经有太多参数了怎么办？而且这个方法也很丑。 - Piotr Perak