在SQL Server 2014中，使用TransactionScope包装带有事务的存储过程

Question

在SQL Server 2014中，使用TransactionScope包装带有事务的存储过程

c#sql-serverstored-proceduresado.nettransactionscope

21

我正在使用C#和ADO.Net以及TransactionScope在ASP.NET应用程序中运行事务。该事务旨在跨多个表保存一些数据，然后向订阅者发送电子邮件。

问题: 当调用一个在SQL Server 2014中有自己的事务的存储过程时，是否可以在TransactionScope中使用它？还是应该从被调用的存储过程中删除SQL事务语句，如begin tran、commit tran和rollback tran语句？

以下是此场景的C#代码和存储过程的T-SQL代码。

C#代码使用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);
    }

存储过程 SaveEmailData 的 T-SQL：

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

- Sunil

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- StuartLC · Accepted Answer

是的，TransactionScope在包装TSQL中的BEGIN / COMMIT TRANSACTION 或ADO中的 SqlConnection.BeginTransaction时仍然有效。在包装单个连接时，行为类似于嵌套事务在Sql中：

每个BEGIN TRAN将增加@@TranCount
COMMIT TRAN将简单地减少@@TRANCOUNT。当@@TRANCOUNT达到零时，事务才会被提交。

然而：

ROLLBACK TRAN将中止整个事务（即@@TRANCOUNT变为零），除非您使用Save Points（即SAVE TRANSACTION xx... ROLLBACK TRANSACTION xx）。
当使用存储过程时，如果连接的@@TRANCOUNT在从进入存储过程时的值与退出SPROC时的值不同时，您将收到错误。

因此，通常更容易将事务语义留给 TransactionScope 并移除任何手动的BEGIN TRAN / COMMIT TRAN逻辑，以避免使您的TSQL混乱。

编辑 - 下面评论的澄清

在OP的情况下，SPROC并没有考虑嵌套事务（即无论是由Sql还是.Net外部事务包装的事务），特别是BEGIN CATCH块中的ROLLBACK将终止整个外部事务，并可能导致更多的错误在外部TransactionScope中，因为未遵守@@TRANCOUNT规则。如果SPROC需要以嵌套或独立的事务方式运行，则应观察此类嵌套事务模式。
SavePoints不能与分布式事务一起使用，TransactionScope可以轻松地升级为分布式事务，例如，如果您正在使用不同的连接字符串或控制其他资源在事务范围内。

因此，我建议将PROC重构为仅包含“happy”核心/内部情况，在Transaction Scope中调用此内部proc，并在那里进行任何异常处理和回滚。如果您还需要从Ad Hoc Sql调用该proc，则提供一个外部包装器Proc来处理异常：

-- 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;