在存储过程中使用"SET XACT_ABORT ON"有什么好处？

Question

SET XACT_ABORT ON 在存储过程中使用的好处是什么？

- odiseh

---

MSDN提供了方便的参考文献：https://msdn.microsoft.com/zh-cn/library/ms188792.aspx - Tim Abell

这是一篇关于错误处理的优秀文章：http://www.sommarskog.se/error_handling/Part1.html - Reversed Engineer

Answer 1

SET XACT_ABORT ON指示SQL Server在运行时出现错误时回滚整个事务并中止批处理。它可以在客户端应用程序上发生命令超时而不是在SQL Server本身内部（这不受默认XACT_ABORT OFF设置的覆盖）的情况下为您提供保护。

由于查询超时会使事务保持打开状态，因此建议在所有具有显式事务的存储过程中使用SET XACT_ABORT ON（除非您有特定原因不这样做），因为应用程序在具有打开事务的连接上执行工作的后果是灾难性的。

Dan Guzman's Blog上有一个非常好的概述（原始链接）

- Ben Griswold

---

为什么默认情况下它没有开启？ - Mike W

如果在 SQL 中使用 BEGIN TRY-BEGIN CATCH 和 ROLLBACK 与 BEGIN CATCH 块，是否仍需要使用 XACT_ABORT？ - user20358

BEGIN TRY-BEGIN CATCH不能捕获客户端应用程序超时等错误，而且一些SQL错误也是无法捕获的，这会导致出现意料之外的未提交事务。 - Tom Lint

Answer 2

在我看来，SET XACT_ABORT ON 已经被 SQL 2k5 中 BEGIN TRY/BEGIN CATCH 的添加所取代。在 Transact-SQL 中引入异常块之前，处理错误非常困难，而不平衡的过程很常见（退出时与进入时的 @@TRANCOUNT 不同的过程）。

通过 Transact-SQL 异常处理的添加，编写正确的过程并确保正确平衡交易变得更加容易。例如，我使用这个异常处理和嵌套事务的模板：

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

它允许我编写原子存储过程，以便在可恢复错误的情况下仅回滚其自身的工作。

Transact-SQL存储过程面临的主要问题之一是数据纯度：有时接收到的参数或表中的数据只是纯错误的，导致重复键错误、参照约束错误、检查约束错误等等。毕竟，这些约束的作用就是防止这些数据纯度错误，如果这些数据纯度错误是不可能的，并且都被业务逻辑捕获，则这些约束将变得过时（为了效果而夸张）。如果XACT_ABORT打开，则所有这些错误会导致整个事务丢失，而无法编写异常块来优雅地处理异常。一个典型的例子是尝试进行INSERT并在PK冲突时回退到UPDATE操作。

- Remus Rusanu

---

除了客户端超时之外... 我的观点是在 SQL 2005 中使用 SET XACT_ABORT 更有效，因为行为更加可预测：批处理中出现终止错误的情况大大减少。 - gbn

我有些赞同，但我会为所有可能出现的情况规划我的错误处理流程，因为我知道如果命令超时发生了，作为开发人员DBA我会受到责备。 - gbn

你会如何处理长时间运行、同步的数据库操作？ - Ian Boyd

MSDN文档中指出：“针对大多数OLE DB提供程序（包括SQL Server），在隐式或显式事务中进行数据修改语句时，必须将XACT_ABORT设置为ON。唯一不需要该选项的情况是如果提供程序支持嵌套事务。” https://msdn.microsoft.com/zh-cn/library/ms188792(v=sql.120).aspx - Nathan

在我看来，通过添加BEGIN TRY/BEGIN CATCH语句，SET XACT_ABORT ON已经过时了。 - 我听到你的意见，但请查看http://www.sommarskog.se/error_handling/Part1.html。 - Reversed Engineer

Answer 3

引用MSDN的说法：

当 SET XACT_ABORT 为 ON 时，如果一个 Transact-SQL 语句引发运行时错误，则整个事务将被终止并回滚。 当 SET XACT_ABORT 为 OFF 时，在某些情况下，只有引发错误的 Transact-SQL 语句被回滚，而事务继续处理。

实际上，这意味着一些语句可能会失败，导致事务“部分完成”，对于调用者可能没有任何失败的迹象。

以下是一个简单的示例：

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

如果 XACT_ABORT 为 OFF，这段代码会执行“成功”并正常结束；如果 XACT_ABORT 为 ON，代码将以错误终止（'INSERT INTO t2' 不会被执行，并且客户端应用程序将引发异常）。

作为一种更灵活的方法，可以在每个语句后检查 @@ERROR（老派方式），或使用 TRY...CATCH 块（MSSQL2005+）。个人而言，在没有某些高级错误处理的情况下，我更喜欢将 XACT_ABORT 设置为 ON。

- VladV

Answer 4

关于客户端超时和使用XACT_ABORT来处理它们，我认为在客户端API（例如SqlClient）中设置超时至少有一个非常好的理由，那就是保护客户端应用程序代码免受发生在SQL服务器代码中的死锁影响。在这种情况下，客户端代码没有问题，但必须保护自己免受永久阻塞等待服务器上的命令完成。因此，反过来说，如果客户端超时必须存在以保护客户端代码，那么XACT_ABORT ON也必须保护服务器代码免受客户端中止的影响，以防服务器代码执行时间比客户端愿意等待的时间长。

- ionutm

Answer 5

在事务管理中使用它可以确保任何错误都会导致事务被回滚。

- Dan Diplo

Answer 6

在这里添加新的更新。最新的MSDN更新展示了如何同时使用XACT_ABORT ON和TRY/CATCH块。MSDN链接

    -- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE usp_GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- A FOREIGN KEY constraint exists on this table. This   
        -- statement will generate a constraint violation error.  
        DELETE FROM Production.Product  
            WHERE ProductID = 980;  
  
    -- If the DELETE statement succeeds, commit the transaction.  
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.
    -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO

- Praveen M P