XACT_ABORT在错误发生时并不总是回滚事务。它在什么情况下确切地执行回滚操作？

Question

问题

SET XACT_ABORT的文档对启用此选项的影响只是简单介绍了一下。

当启用SET XACT_ABORT时，如果Transact-SQL语句引发运行时错误，则整个事务将被终止并回滚。

我不认为这就是全部。阅读完后，我担心如果由外部进程创建的事务执行启用此选项的存储过程，则可能会回滚外部事务。幸运的是，我的担忧被证明是没有根据的。然而，这意味着我现在并不真正了解XACT_ABORT的工作原理。SQL Server检查何种条件以确定是否应回滚事务？

先前调查

我进行了以下实验：（以下是代码摘要，因为在代码块之前有编号列表会破坏StackOverflow的格式）

CREATE TABLE Dummy
(
    ID INT NOT NULL IDENTITY CONSTRAINT PK_Dummy PRIMARY KEY,
    Text NVARCHAR(128) NOT NULL
)

CREATE UNIQUE NONCLUSTERED INDEX IX_Dummy_Text ON dbo.Dummy(Text)

GO 

CREATE OR ALTER PROCEDURE InsertDummy
    @Text NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT OFF
    SET XACT_ABORT ON

    INSERT dbo.Dummy (Text) VALUES (@Text)
END

GO

SET XACT_ABORT ON

BEGIN TRANSACTION
BEGIN TRY
    EXEC dbo.InsertDummy @Text = N'Dummy'
    EXEC dbo.InsertDummy @Text = N'Dummy' --DUPLICATE!
END TRY
BEGIN CATCH
    PRINT 'ERROR! @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)

    -- Echo the error
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT @ErrorMessage = ERROR_MESSAGE();  
    SELECT @ErrorSeverity = ERROR_SEVERITY();  
    SELECT @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, -- Message text.  
                @ErrorSeverity, -- Severity.  
                @ErrorState -- State.  
                );  
END CATCH

PRINT 'At the end @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)
IF @@TRANCOUNT>0
    ROLLBACK

1. 创建一个带有唯一索引的虚拟表
2. 一个插入到Dummy表的存储过程。该过程启用XACT_ABORT。
3. 执行此存储过程两次的代码，处于事务中。第二次调用失败，因为它试图将重复值插入到Dummy表中。
4. 相同的代码打印出@@TRANCOUNT的值，以显示我们是否仍在事务中。它还启用了XACT_ABORT。

此测试的输出结果为：

(1 row affected)

(0 rows affected)
ERROR! @@TRANCOUNT is 1
Msg 50000, Level 14, State 1, Line 74
Cannot insert duplicate key row in object 'dbo.Dummy' with unique index 'IX_Dummy_Text'. The duplicate key value is (Dummy).
At the end @@TRANCOUNT is 1

一个错误被引发，但事务没有回滚。这个设置的工作方式显然不像文档所说的那么简单。为什么事务没有回滚？这个答案提到，只有当错误的严重程度至少为16时，XACT_ABORT才会回滚事务。这个例子中的错误级别只有14。然而，即使我用RAISERROR(N'Custom error', 16, 0)替换存储过程中的INSERT，事务仍然没有回滚。更新：我发现，尽管在我的测试中事务没有回滚，但它已经失败了！无论XACT_ABORT设置如何，当我执行这个示例时，@@TRANCOUNT都是1：但如果设置为ON，XACT_STATE()是-1，表示未提交的事务。当XACT_ABORT是OFF时，XACT_STATE()是1。

- kamilk

---

你需要知道的是，这些语句中有嵌套事务，但实际上这是一个误解。 - Thom A

我建议阅读这篇文章。在T-SQL中进行错误处理非常复杂，而且你无法从文档中获得完整的信息。 - Jeroen Mostert

@Larnu，实际上我并不这样做：此示例中的过程仅会创建一个保存点。抱歉，也许我应该通过删除这个条件逻辑来简化示例，但我想坚持SAVE TRANSACTION文档中建议的模式以及我的实际生产代码过程中使用的模式。 - kamilk

你在样例中确实这样做了，@kamilk。你首先使用BEGIN TRANSACTION开始一个事务，然后跟着执行EXEC dbo.InsertDummy。在dbo.InsertDummy内部，你又有了另一个BEGIN TRANSACTION。因此是嵌套事务。 - Thom A

@Larnu 第二个 BEGIN TRANSACTION 不会被执行，因为 @TranCounter 将等于 1。 - kamilk

@Larnu 我现在已经简化了示例。 - kamilk

Answer 1

问题：“出现错误但事务没有回滚。这些设置的工作方式显然不像文档所说的那么简单。为什么事务没有回滚？”

答案是RAISERROR不会触发XACT_ABORT！这意味着我们在事务方面可能处于非常混乱的状态。 放弃，放弃，我们正在XACT_ABORT中，还是？！

根据MSDN的说明，

THROW语句遵从SET XACT_ABORT指令，而RAISERROR则不遵从。新的应用程序应该使用THROW代替RAISERROR。

我们可以使用THROW语句代替RAISERROR。因此，我们可以使用以下语句来触发XACT_ABORT：

TRUNCATE TABLE Dummy
GO
SET XACT_ABORT ON

BEGIN TRANSACTION
BEGIN TRY
    EXEC dbo.InsertDummy @Text = N'Dummy'
    EXEC dbo.InsertDummy @Text = N'Dummy' --DUPLICATE!
END TRY
BEGIN CATCH
THROW
END CATCH

PRINT 'At the end @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)
IF @@TRANCOUNT>0
    ROLLBACK

输出结果将是：

(1 row affected)

(0 rows affected)
Msg 2601, Level 14, State 1, Procedure dbo.InsertDummy, Line 7 [Batch Start Line 5]
Cannot insert duplicate key row in object 'dbo.Dummy' with unique index 'IX_Dummy_Text'. The duplicate key value is (Dummy).

关于此问题的更新，请参见将set xact_abort on和try-catch一起使用

- Esat Erkec