c# - 如何在不升级到 MSDTC 的情况下在 SQLCLR 中使用 TransactionScope

Question

我们的许多 DAL 代码都使用 TransactionScope 进行交易。这很好用，但是当我从 SQLCLR 过程内部使用此 DAL 代码时出现问题。事务升级到 MSDTC，这是我不想要的。

问题很容易重现:

1. CLR 实现

   [SqlProcedure]
   public static void ClrWithScope(string cmdText)
   {
       /* escalates to MSDTC when a transaction is already open */
       using ( var scope = new TransactionScope())
       {
           using (var connection = new SqlConnection("context connection=true;"))
           {
               connection.Open();
               using (var cmd = new SqlCommand(cmdText, connection))
               {
                   SqlContext.Pipe.ExecuteAndSend(cmd);
               }
           }
           scope.Complete();
       }
   }
   
   [SqlProcedure]
   public static void ClrWithTrans(string cmdText)
   {
       /* works as expected (without MSDTC escalation ) */
       using (var connection = new SqlConnection("context connection=true;"))
       {
           connection.Open();
           using (var tx = connection.BeginTransaction())
           {
               using (var cmd = new SqlCommand(cmdText, connection, tx))
               {
                   SqlContext.Pipe.ExecuteAndSend(cmd);
                   tx.Commit();
               }
           }
       }
   }
   

2. 用于执行 CLR 过程的 SQL 脚本

   BEGIN TRANSACTION
   
   exec dbo.ClrWithTrans "select * from sys.tables";
   exec dbo.ClrWithScope "select * from sys.tables"; /* <- DOES NOT WORK! */
   
   ROLLBACK TRANSACTION
   

3. 错误

   Msg 6549, Level 16, State 1, Procedure ClrWithScope, Line 0
   A .NET Framework error occurred during execution of user defined routine or aggregate 'clrClrWithScope': 
   System.Transactions.TransactionAbortedException: Die Transaktion wurde abgebrochen. ---> System.Transactions.TransactionPromotionException: MSDTC on server 'BLABLA' is unavailable. ---> System.Data.SqlClient.SqlException: MSDTC on server 'BLABLA' is unavailable.
   System.Data.SqlClient.SqlException: 
      bei System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
      bei System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
   System.Transactions.TransactionPromotionException: 
      bei System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
      bei System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
      bei System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   System.Transactions.TransactionAbortedException: 
      bei System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx)
      bei System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)
      bei System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)
      bei System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)
      bei System.Transactions.TransactionScope.PushScope()
      bei System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption)
      bei Giag.Silo.Data.SqlClr.ClrWithScope(String cmdText)
   . User transaction, if any, will be rolled back.
   

没有“BEGIN TRANSACTION”语句，dbo.ClrWithScope 调用工作正常。我想在 .Net Framework 中登记时不考虑由 SQLServer 启动的事务。

有没有办法解决这个问题。一个想法是手动创建一个 SqlTransaction 并使 TransactionScope 使用此事务，但我不知道该怎么做。另一种解决方案是在所有 DAL 代码中创建一个特例(实现起来并不有趣)。

有什么想法吗？

Answer 1

在 SQL CLR 中使用 TransactionScope 将始终提升/升级为 MSDTC 事务。似乎没有任何办法解决这个问题，即使在 SQL 2012 中也是如此。

来自 TechNet 关于 SQL CLR 和 TransactionScope ( http://technet.microsoft.com/en-us/library/ms131084.aspx )

TransactionScope should be used only when local and remote data sources or external resource managers are being accessed. This is because TransactionScope always causes transactions to promote, even if it is being used only within a context connection.