SQL Server 中SET XACT_ABORT设置的作用
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。