将TransactionScope与存储过程事务一起使用不起作用 [英] Using TransactionScope with Stored Procedure Transaction does not work

问题描述

据我了解，将T-SQL BEGIN/COMMIT TRANSACTION 包装在存储过程中时，C# TransactionScope 仍然可以工作.

As per my understanding, C# TransactionScope can still work when wrapping a T-SQL BEGIN / COMMIT TRANSACTION inside a stored procedure.

我有以下C#方法，该方法首先进行 EF Save ，然后调用具有自己事务的存储过程，然后通过HTTP调用外部服务

I have the following C# method which does EF Save first, then call a stored procedure that has its own transaction and then call external service over HTTP

public async Task DoSomething(MyDto dto)
{
        using (var scope = new TransactionScope())
        {
            //Save First
            var myEntity = await _dbContext.MyEntity.Where(x=>x.Id == dto.Id).SingleOtDefaultAsync();

            // Assign properties here from dto to MyEntity and then save entity             
            await _dbContext.SaveChangesAsync();

            // call stored procedure that has its own transaction
            _dbContext.prcDoExtraWork(dto.Id);

            // call external service using Http             
            await _httpClient.PostAsync(url,somecontent)

            scope.Complete();
        }
}

存储过程:

CREATE PROCEDURE [dbo].[prcDoExtraWork]
    @ID INT 
AS
BEGIN
    SET NOCOUNT ON;
    SET ANSI_WARNINGS ON;
    SET XACT_ABORT ON;     

    BEGIN TRY
        BEGIN TRANSACTION
            // modify data and  inserts records into tables
        COMMIT TRANSACTION

        SELECT 1 AS `Result`
    END TRY
    BEGIN CATCH
        IF (XACT_STATE() <> 0)
        BEGIN       
            ROLLBACK TRANSACTION

            IF @ErrorMessage IS NULL
            BEGIN
                SET @ProcName = ERROR_PROCEDURE();
                SET @ErrorMessage = ERROR_MESSAGE();
                SET @ErrorNumber = ERROR_NUMBER();
                SET @ErrorSeverity = ERROR_SEVERITY();
                SET @ErrorState = ERROR_STATE();
            END

            EXEC prcErrorHandler @ProcName = @ProcName, 
                                 @ErrorMessage = @ErrorMessage, 
                                 @ErrorSeverity = @ErrorSeverity,
                                 @ErrorState = @ErrorState, 
                                 @ErrorNumber = @ErrorNumber

            SELECT 0 AS `Result`
        END
    END CATCH

    SET XACT_ABORT OFF;
END

问题1:通过http调用外部服务失败，我希望插入或修改的任何记录存储过程都将回滚.

Issue 1: the call of the external service over http fails, my expectation was whatever records stored procedure has inserted or modified will ROLLBACK.

但是这没有发生.我仍然在数据库中看到新记录

However that is not happening. I still see the new records in the database

问题2

要解决上述错误，由于我使用的是异步方法，因此我必须启用TransactionScopeAsyncFlowOption

To solve the error above I had to enable TransactionScopeAsyncFlowOption since i am using async methods

using (var scope = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled))
    {

     // do your stuff
     scope.complete();
   }

但是现在在scope.complete()上我得到了错误

However now on scope.complete() i get error

由于存在以下原因，无法执行交易操作正在处理此事务的待处理请求

The transaction operation cannot be performed because there are pending requests working on this transaction

System.Transactions.TransactionAbortedException:事务具有流产了.---> System.Data.SqlClient.SqlException:事务无法执行操作，因为有待处理的请求正在处理此交易.在System.Data.SqlClient.SqlConnection.OnError(SqlException异常，布尔值breakConnection，在以下位置执行Action'1 wrapCloseInAction)System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj，布尔值调用者HasConnectionLock，布尔值asyncClose)System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObjectstateObj，布尔值和dataReady)System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObjectstateObj)在System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte []缓冲区，TransactionManagerRequestType请求，字符串transactionName，TransactionManagerIsolationLevel isoLevel，Int32超时，SqlInternalTransaction事务，TdsParserStateObject stateObj，布尔值isDelegateControlRequest)System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequesttransactionRequest，字符串transactionName，IsolationLevel iso，SqlInternalTransaction internalTransaction，布尔isDelegateControlRequest)System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment征集)-内部异常堆栈跟踪的结尾-在System.Transactions.TransactionStateAborted.EndCommit(InternalTransactiontx)位于System.Transactions.CommittableTransaction.Commit()处System.Transactions.TransactionScope.InternalDispose()在System.Transactions.TransactionScope.Dispose()在XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.d__343.MoveNext()---从之前引发异常的位置开始的堆栈跟踪-System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()在System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(任务任务)在XXXXXXXXXXXXX.MyDetailController.d__9.MoveNext()---从之前引发异常的位置开始的堆栈跟踪-System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()在System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(任务任务)System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResultasyncResult)System.Web.Mvc.Async.AsyncControllerActionInvoker.<> c__DisplayClass37.b__36(IAsyncResultasyncResult)System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResultasyncResult)System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3d()在System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<> c__DisplayClass46.b__3f()在System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<> c__DisplayClass46.b__3f()在System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<> c__DisplayClass46.b__3f()在System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResultasyncResult)System.Web.Mvc.Async.AsyncControllerActionInvoker.<> c__DisplayClass21.<> c__DisplayClass2b.b__1c()在System.Web.Mvc.Async.AsyncControllerActionInvoker.<> c__DisplayClass21.b__1e(IAsyncResultasyncResult)

System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Data.SqlClient.SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment) --- End of inner exception stack trace --- at System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx) at System.Transactions.CommittableTransaction.Commit() at System.Transactions.TransactionScope.InternalDispose() at System.Transactions.TransactionScope.Dispose() at XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.d__343.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at XXXXXXXXXXXXX.MyDetailController.d__9.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.b__36(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3d() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.b__1c() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.b__1e(IAsyncResult asyncResult)

推荐答案

我认为我找到了它.
我通过启用 TransactionScopeAsyncFlowOption 解决了第一个问题，因为我使用的是异步方法

I think i found it.
The first issue i solved by enabling TransactionScopeAsyncFlowOption since i am using async methods

        using (var scope = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled))
        {

         // do your stuff
         scope.complete();
       }

第二期
基于成功或错误，存储的proc返回1或0.C#代码不在乎返回结果的值，但是我没有评估存储过程的返回结果.所以要解决我必须调用'SingleOrDefault`

The second issue
The stored proc was returning 1 or 0 based on success or error. The C# code does not care the value of the return result however i was not evaluating the return result from stored proc. So to solve i have to call 'SingleOrDefault`

_dbContext.prcDoExtraWork(dto.Id).SingleOrDefault()

这篇关于将TransactionScope与存储过程事务一起使用不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！