Using TransactionScope around a stored procedure with transaction in SQL Server 2014

Question

I am using C# and ADO.Net with a TransactionScope to run a transaction in an ASP.Net app. This transaction is supposed to save some data across multiple tables and then send an email to subscribers.

Question: is it a valid use of TransactionScope, when it includes a call to a stored procedure that has its own transaction in SQL Server 2014, or should I remove the SQL transaction statements i.e. begin tran, commit tran and rollback tran statements from the stored procedure being called within this TransactionScope?

The C# code for this scenario and also the T-SQL code of stored procedure are both mentioned below.

C# code using TransactionScope:

  try 
    {
        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                // Opening the connection automatically enlists it in the  
                // TransactionScope as a lightweight transaction.
                connection1.Open();

                // SaveEmailData is a stored procedure that has a transaction within it
                SqlCommand command1 = new SqlCommand("SaveEmailData", connection1);
                command1.CommandType = CommandType.StoredProcedure;
                command1.ExecuteNonQuery();

            }

            //Send Email using the helper method
            EmailHelper.SendCustomerEmails(customerIds);

            // The Complete method commits the transaction. If an exception has been thrown, 
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();

        }
    }
    catch( Exception ex)
    {
       Logger.Log(ex);
    }

T-SQL of stored procedure SaveEmailData:

SET NOCOUNT ON

    BEGIN TRY
        DECLARE @emailToUserId BIGINT

        BEGIN TRAN
        -- //update statement. detail statement omitted
        UPDATE TABLE1...

         --update statement. detail statement omitted
        UPDATE TABLE2...

        IF @@trancount > 0
        BEGIN
            COMMIT TRAN
        END
    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
        END

        EXEC Error_RaiseToADONET

    END CATCH

Answer 1

Yes, TransactionScope can still work when wrapping a TSQL BEGIN / COMMIT TRANSACTION or an ADO SqlConnection.BeginTransaction. When wrapping a single connection, the behaviour is similar to nesting transactions in Sql:

• @@TranCount will be incremented on each BEGIN TRAN

• COMMIT TRAN will simply decrement @@TRANCOUNT. The transaction will only be committed if @@TRANCOUNT hits zero.

However:

• ROLLBACK TRAN will abort the whole transaction (i.e. @@TRANCOUNT to zero), unless you are using Save Points (i.e. SAVE TRANSACTION xx ... ROLLBACK TRANSACTION xx.
• When using stored procedures, you will receive an error if the connection's @@TRANCOUNT differs when exiting the SPROC from the value it had when entering a SPROC.

As a result, it is typically much easier to leave transaction semantics to TransactionScope and remove any manual BEGIN TRAN / COMMIT TRAN logic from cluttering up your TSQL.

Edit - clarification of the comments below

• In the OP's case, the SPROC has NOT been written with nested transactions in mind (i.e. whether wrapped by an Sql or .Net outer transaction), specifically, the ROLLBACK in the BEGIN CATCH block will abort the entire outer transaction and will likely cause further errors in the outer TransactionScope as the @@TRANCOUNT rule has not been adhered to. A nested transaction pattern such as this should be observed if a SPROC needs to operate in both a nested or standalone transaction fashion.

• SavePoints do not work with Distributed transactions, and TransactionScope can easily escalate into a distributed transaction e.g. if you are using different connection strings or controlling other resources in under the transaction scope.

As a result, I would recommend refactoring the PROC into a just the 'happy' core / inner case, calling this inner proc from the Transaction Scope, and doing any exception handling and rollback there. If you also need to call the proc from Ad Hoc Sql, then provide an external wrapper Proc which has the exception handling:

-- Just the happy case. This is called from .Net TransactionScope
CREATE PROC dbo.InnerNonTransactional
  AS
    BEGIN 
      UPDATE TABLE1...
      UPDATE TABLE2 ....
    END;

-- Only needed if you also need to call this elsewhere, e.g. from AdHoc Sql
CREATE PROC dbo.OuterTransactional
  AS
    BEGIN
      BEGIN TRY
        BEGIN TRAN
            EXEC dbo.InnerNonTransactional
        COMMIT TRAN
      END TRY
      BEGIN CATCH
         -- Rollback and handling code here.
      END CATCH
    END;