SQL Server 中SET XACT_ABORT设置的作用

• SET XACT_ABORT
  • 没有外部transaction 和 try...catch
    - 设置为OFF
    - 设置为ON
  • 只有try...catch
  • 有transaction和try...catch
    - 设置为OFF
    - 设置为ON
  • 默认设置
  • 参考链接

SET XACT_ABORT

决定遇到runtime error的时候transaction能否继续，大佬们都推荐设置为ON(参考文章末尾链接)。

没有外部transaction 和 try...catch

简单的例子如下：

--先创建测试表
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE table tb1(id int)
GO

设置为OFF

第一句执行出错，仍然会继续执行第二句，最后的select会得到结果1，同时会有error message "Divide by zero error encountered.".

SET XACT_ABORT OFF

INSERT INTO tb1 VALUES (1/0)    
INSERT INTO tb1 VALUES (1)    
select * from tb1

设置为ON

在第一个 insert语句就出错了，后面的insert和select都不会执行。只有一个error message “Divide by zero error encountered.”

SET XACT_ABORT ON

INSERT INTO tb1 VALUES (1/0)    
INSERT INTO tb1 VALUES (2)    
select * from tb1

只有try...catch

示例代码如下, 结果是不论On/Off都执行成功，返回数据集1

SET XACT_ABORT ON --OFF
GO
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE table tb1(id int)
GO

IF OBJECT_ID('pTestXactAbort') IS NOT NULL
DROP PROC pTestXactAbort
GO
create procedure pTestXactAbort
as
begin
    begin try
                raiserror('pTestXactAbort', 16,1)
    end try
    begin catch
        INSERT tb1(id) VALUES(1)
        select * from tb1
    end catch   
end
go


exec pTestXactAbort

有transaction和try...catch

示例代码如下：

IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE table tb1(id int)
GO

IF OBJECT_ID('pTestXactAbort') IS NOT NULL
DROP PROC pTestXactAbort
GO
create procedure pTestXactAbort
as
begin
    begin try
            begin transaction
                raiserror('pTestXactAbort', 16,1)
                
            commit transaction;
    end try
    begin catch
        INSERT tb1(id) VALUES(1)
        select * from tb1
        ROLLBACK TRAN
    end catch   
end
go

设置为OFF

执行成功的，得到结果“1”

在catch到error之后，catch部分仍然可以继续执行其他的修改数据的代码。

SET XACT_ABORT OFF
GO
exec pTestXactAbort

设置为ON

执行失败并得到错误"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.".

SET XACT_ABORT ON
GO
exec pTestXactAbort

需要注意的是此时在catch中必须先rollback transaction,再处理其他操作。(如果只是select操作也不会出错，但所有会写数据库transaction log的操作都会出错)

如果把sp的定义中try...catch的部分改成先rollback tran，再处理其他操作就可以执行成功，如下：

IF OBJECT_ID('pTestXactAbort') IS NOT NULL
DROP PROC pTestXactAbort
GO
create procedure pTestXactAbort
as
begin
    begin try
            begin transaction
                raiserror('pTestXactAbort', 16,1)
                
            commit transaction;
    end try
    begin catch
        
        ROLLBACK TRAN
        INSERT tb1(id) VALUES(1)
        select * from tb1
    end catch   
end
go

默认设置

需要注意从不同应用程序生成的数据库连接XACT_ABORT的默认设置是有可能不同的，曾经遇到过同一个sp同样的参数在management studio中可以顺利执行成功，但在应用程序中调用却执行一半就出错停止的问题。经调查就是因为management studio 中 XACT_ABORT默认设置为OFF，但程序里默认是ON。

参考链接

• https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

• http://www.sommarskog.se/error_handling/Part1.html
• https://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure

• https://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx

转载于:https://www.cnblogs.com/sqlzh/p/9647105.html