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.

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

由于查询超时将使事务保持打开状态，建议在所有具有显式事务的存储过程中使用SET 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

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

在我看来，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.