它用于事务管理，以确保任何错误都会导致事务回滚。

SET XACT_ABORT ON指示SQL Server在发生运行时错误时回滚整个事务并中止批处理。它涵盖了在客户端应用程序上而不是SQL Server本身(默认的XACT_ABORT OFF设置不包括)发生命令超时的情况。

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

丹·古兹曼的博客上有一篇很棒的概述，

在我看来，SET XACT_ABORT ON在SQL 2k5中被添加了BEGIN TRY/BEGIN CATCH而过时了。在Transact-SQL中出现异常块之前，处理错误非常困难，而且不平衡的过程非常普遍(过程在退出时的@@TRANCOUNT与进入时的@@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

它允许我编写原子过程，在出现可恢复错误时只回滚它们自己的工作。

One of the main issues Transact-SQL procedures face is data purity: sometimes the parameters received or the data in the tables are just plain wrong, resulting in duplicate key errors, referential constrain errors, check constrain errors and so on and so forth. After all, that's exactly the role of these constrains, if these data purity errors would be impossible and all caught by the business logic, the constrains would be all obsolete (dramatic exaggeration added for effect). If XACT_ABORT is ON then all these errors result in the entire transaction being lost, as opposed to being able to code exception blocks that handle the exception gracefully. A typical example is trying to do an INSERT and reverting to an UPDATE on PK violation.

引用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。

Regarding client timeouts and the use of XACT_ABORT to handle them, in my opinion there is at least one very good reason to have timeouts in client APIs like SqlClient, and that is to guard the client application code from deadlocks occurring in SQL server code. In this case the client code has no fault, but has to protect it self from blocking forever waiting for the command to complete on the server. So conversely, if client timeouts have to exist to protect client code, so does XACT_ABORT ON has to protect server code from client aborts, in case the server code takes longer to execute than the client is willing to wait for.

在这里添加新的更新。最新的MSDN更新显示了如何使用XACT_ABORT ON和TRY/CATCH Block。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