Using TransactionScope with Stored Procedure Transaction does not work

Question

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

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

Stored procedure:

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

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

Issue 2

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

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: 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)

LP13 LP13 · Accepted Answer · 2018-07-03T15:53:05

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

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

Using TransactionScope with Stored Procedure Transaction does not work

1 Answers