很多我们的DAL代码使用TransactionScope来进行事务处理。这个方法很好用,但当我从SQLCLR过程中使用此DAL代码时,会出现一个问题——事务被升级到MSDTC,而这不是我想要的。
这个问题很容易复现:
有没有解决办法?一个想法是手动创建SqlTransaction并使TransactionScope使用此事务,但我不知道该如何实现。另一种解决方法是在所有DAL代码中进行特殊处理(实现起来并不好玩)。
有什么想法吗?
这个问题很容易复现:
CLR Implementation
[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(); } } } }
SQL script used to execute the CLR procedure
BEGIN TRANSACTION exec dbo.ClrWithTrans "select * from sys.tables"; exec dbo.ClrWithScope "select * from sys.tables"; /* <- DOES NOT WORK! */ ROLLBACK TRANSACTION
the error
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.
有没有解决办法?一个想法是手动创建SqlTransaction并使TransactionScope使用此事务,但我不知道该如何实现。另一种解决方法是在所有DAL代码中进行特殊处理(实现起来并不好玩)。
有什么想法吗?