sql - XACT_ABORT 并不总是在出错时回滚事务。它什么时候做？

Question

问题

The documentation的 SET XACT_ABORT关于启用此选项的效果，仅此而已。

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

我不相信这是全部真相。读完后，我担心如果在外部进程创建的事务中执行启用此选项的存储过程，它可能最终会回滚外部事务。幸运的是，我的担心被证明是没有根据的。然而，这意味着现在我并不真正理解 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

创建一个带有唯一索引的虚拟表

插入到 Dummy 中的存储过程。程序启用 XACT_ABORT .

在事务中执行此过程两次的代码。第二次调用失败，因为它试图将重复值插入到 Dummy 中。

相同的代码打印出 @@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

出现错误但事务未回滚。这个设置的工作方式显然不像文档让我相信的那么简单。为什么事务没有回滚？

This answer提到 XACT_ABORT如果错误的严重程度至少为 16，则仅回滚事务。此示例中的错误仅为 14 级。但是，即使我替换了 INSERT在程序中使用 RAISERROR (N'Custom error', 16, 0) ，事务仍然没有回滚。

更新:我发现虽然我的测试中事务没有回滚，但它是注定的! @@TRANCOUNT是 1无论 XACT_ABORT，当我执行此示例时设置:但如果设置为 ON , XACT_STATE()是 -1 ，表示不可提交的事务。当 XACT_ABORT是 OFF , XACT_STATE()是 1 .

Answer 1

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

The answer to that is that RAISERROR will not cause XACT_ABORT to trigger! This means we can be in a very messed up state transaction wise Abort, Abort, We Are XACT_ABORT:ing, Or Are We?!

根据 MSDN，

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of 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 and try-catch together