TransactionScope 和事务

问题描述

在我的 C# 代码中，我使用的是 TransactionScope，因为我被告知不要依赖我的 sql 程序员将始终使用事务并且我们负责和 yada yada.

In my C# code I am using TransactionScope because I was told not to rely that my sql programmers will always use transactions and we are responsible and yada yada.

说到这里

看起来像TransactionScope对象在SqlTransaction之前回滚了?这可能吗?如果可能，在事务中包装 TransactionScope 的正确方法是什么.

It looks like TransactionScope object Rolls back before the SqlTransaction? Is that possible and if so what is the correct methodology for wrapping a TransactionScope in a transaction.

这里是sql测试

CREATE PROC ThrowError
AS

BEGIN TRANSACTION --SqlTransaction
SELECT 1/0

IF @@ERROR<> 0
BEGIN
  ROLLBACK TRANSACTION --SqlTransaction
  RETURN -1 
END
ELSE
BEGIN
  COMMIT TRANSACTION --SqlTransaction
  RETURN 0
END

go

DECLARE @RESULT INT

EXEC @RESULT = ThrowError

SELECT @RESULT

如果我运行它，我只会得到除以 0 并返回 -1

And if I run this I get just the divide by 0 and return -1

从 C# 代码调用我收到一条额外的错误消息

Call from the C# code I get an extra error message

遇到除以零错误.
EXECUTE 后的事务计数表明缺少 COMMIT 或 ROLLBACK TRANSACTION 语句.先前计数 = 1，当前计数 = 0.

如果我给 sql 事务一个名字，那么

If I give the sql transaction a name then

无法回滚 SqlTransaction.未找到该名称的事务或保存点.EXECUTE 后的事务计数表示 COMMIT 或 ROLLBACK缺少 TRANSACTION 语句.先前计数 = 1，当前计数 = 2.

有时似乎计数会增加，直到应用程序完全退出

some times it seems the count goes up, until the app completely exits

C# 只是

        using (TransactionScope scope = new TransactionScope())
        {
             ... Execute Sql 

             scope.Commit()
         }

sql 代码必须适用于 2000 和 2005

The sql code has to work for 2000 and 2005

推荐答案

SQL Server 2005 中的错误处理进行了大规模升级.这些文章相当广泛:Erland Sommarskog 编写的 SQL 2005 及更高版本中的错误处理 和 SQL 2000 中的错误处理——Erland Sommarskog 的背景

There was a massive upgrade to the error handling within SQL Server 2005. These articles are fairly extensive: Error Handling in SQL 2005 and Later by Erland Sommarskog and Error Handling in SQL 2000 – a Background by Erland Sommarskog

最好的方法是这样的:

创建您的存储过程，如:

Create your stored procedure like:

CREATE PROCEDURE YourProcedure
AS
BEGIN TRY
    BEGIN TRANSACTION --SqlTransaction
    DECLARE @ReturnValue int
    SET @ReturnValue=NULL

    IF (DAY(GETDATE())=1 --logical error
    BEGIN
        SET @ReturnValue=5
        RAISERROR('Error, first day of the month!',16,1) --send control to the BEGIN CATCH block
    END

    SELECT 1/0  --actual hard error

    COMMIT TRANSACTION --SqlTransaction
    RETURN 0

END TRY
BEGIN CATCH
    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION --only rollback if a transaction is in progress
    END

    --will echo back the complete original error message to the caller
    --comment out if not needed
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int

    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

    RETURN ISNULL(@ReturnValue,1)

END CATCH

GO

然而，这仅适用于 SQL Server 2005 及更高版本.如果不使用 SQL Server 2005 中的 TRY-CATCH 块，您将很难删除 SQL Server 发回的所有消息.您所指的 额外消息 是由使用@@trancount 处理回滚的性质引起的:

however that is only for SQL Server 2005 and up. Without using the TRY-CATCH blocks in SQL Server 2005, you have a very difficult time removing all of the messages that SQL Server sends back. The extra messages you refer to are caused by the nature of how rollbacks are handled using @@trancount:

来自 http://www.sommarskog.se/error-handling-I.html#trancount

@@trancount 是一个全局变量体现嵌套层次交易.每个开始交易将@@trancount 增加 1，并且每个COMMIT TRANSACTION 减少@@trancount 加 1.实际上什么都不是提交直到@@trancount 达到 0.ROLLBACK TRANSACTION 回滚一切都到最外层开始TRANSACTION(除非您使用了相当奇特的保存交易)，和强制@@trancount 为 0，问候之前的值.

@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.

当你退出一个存储过程时，如果@@trancount 不一样过程中的价值开始执行，SQL Server 引发错误 266.未引发此错误，但是，如果调用该过程从触发器，直接或间接地.如果你正在运行 SET IMPLICIT交易时间

When you exit a stored procedure, if @@trancount does not have the same value as it had when the procedure commenced execution, SQL Server raises error 266. This error is not raised, though, if the procedure is called from a trigger, directly or indirectly. Neither is it raised if you are running with SET IMPLICIT TRANSACTIONS ON

如果您不想收到有关交易计数不匹配的警告，您只需在任何时间打开一个交易.您可以通过像这样创建所有程序来做到这一点:

If you don't want to get the warning about the transaction count not matching, you need to only have one transaction open at any one time. You do this by creating all of your procedure like this:

CREATE PROC YourProcedure
AS
DECLARE @SelfTransaction char(1)
SET @SelfTransaction='N'

IF @@trancount=0
BEGIN
    SET @SelfTransaction='Y'
    BEGIN TRANSACTION --SqlTransaction
END

SELECT 1/0

IF @@ERROR<> 0
BEGIN
    IF @SelfTransaction='Y'
    BEGIN
        ROLLBACK TRANSACTION --SqlTransaction
    END
    RETURN -1 
END
ELSE
BEGIN
    IF @SelfTransaction='Y'
    BEGIN
        COMMIT TRANSACTION --SqlTransaction
    END
    RETURN 0
END

GO

通过这样做，您只有在您尚未参与交易时才发出交易命令.如果您以这种方式对所有过程进行编码，则只有发出 BEGIN TRANSACTION 的过程或 C# 代码才会实际发出 COMMIT/ROLLBACK，并且事务计数将始终匹配(不会出现错误).

By doing this, you only issue the transaction commands if you are not already in a transaction. If you code all of your procedures this way, only the procedure or the C# code that issues the BEGIN TRANSACTION will actually issue the COMMIT/ROLLBACK and the transaction counts will always match (you won't get an error).

在 C# 中来自 TransactionScope 类文档:

static public int CreateTransactionScope(
    string connectString1, string connectString2,
    string commandText1, string commandText2)
{
    // Initialize the return value to zero and create a StringWriter to display results.
    int returnValue = 0;
    System.IO.StringWriter writer = new System.IO.StringWriter();

    try
    {
        // Create the TransactionScope to execute the commands, guaranteeing
        // that both commands can commit or roll back as a single unit of work.
        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();

                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                returnValue = command1.ExecuteNonQuery();
                writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

                // If you get here, this means that command1 succeeded. By nesting
                // the using block for connection2 inside that of connection1, you
                // conserve server and network resources as connection2 is opened
                // only when there is a chance that the transaction can commit.   
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                    // The transaction is escalated to a full distributed
                    // transaction when connection2 is opened.
                    connection2.Open();

                    // Execute the second command in the second database.
                    returnValue = 0;
                    SqlCommand command2 = new SqlCommand(commandText2, connection2);
                    returnValue = command2.ExecuteNonQuery();
                    writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                }
            }

            // 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 (TransactionAbortedException ex)
    {
        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
    }
    catch (ApplicationException ex)
    {
        writer.WriteLine("ApplicationException Message: {0}", ex.Message);
    }

    // Display messages.
    Console.WriteLine(writer.ToString());

    return returnValue;
}

只是一个想法，但您可以使用 TransactionAbortedException 捕获来获取实际错误并忽略事务计数不匹配警告.

Just a thought, but you might be able to use the TransactionAbortedException catch to get the actual error and ignore the transaction count mismatch warning.

这篇关于TransactionScope 和事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！