set xact_abort on 回滚有关问题

set xact_abort on 回滚问题
本帖最后由 drsheldoncooper 于 2013-03-22 23:31:10 编辑 MSSQL2008，运行2段代码：
运行1:

create table a (x char(10),y int)
go
create trigger [dbo].[a_limit] on [dbo].[a]
for insert
as
if exists(select 1 from inserted where y>0)
  raiserror('错误',16,1)
go
set xact_abort on
begin tran
insert into a values('001',1)
insert into a values('002',2)
commit tran
go
--------结果---------
x               y
001       1
002       2

运行2:

create table a (x char(10),y int)
go
create trigger [dbo].[a_limit] on [dbo].[a]
instead of insert
as
if exists(select 1 from inserted where y>1)
  raiserror('错误',16,1)
go
set xact_abort on
insert into a values('001',1)
insert into a values('002',2)
go
--------结果---------
x               y

问题1：set xact_abort on是否一定要跟begin tran/***/commit tran才能回滚？
问题2：运行1的表a加了for触发器，插入操作加了事务，2句插入语句都抛出错误，为什么事务没有回滚？
问题3：运行2的表a加了instead of触发器，插入操作没有加事务，第2句插入语句抛出错误，为什么第1条插入语句也回滚了？
问题4：set xact_abort on/begin tran/***/commit tran和begin tran/try***/catch***/commit tran有什么区别？
[解决办法]
问题1：是的，xact_abor只是个选项，要配合显示事务才有效果
问题2：你定义的错误只能被try catch捕获处理，不能引发tran的回滚，就如你定义的只是个提示，并没有真正意义上事务的错误
问题3：如2所说，其实并没有引发回滚，而是你定义的instead of触发器代替了insert操作，只给出了一个提示
问题4：加上try catch的话就可以处理你定义的那种错误提示了，可以转到catch语句块中进行显示rollback操作
[解决办法]

引用:

 set xact_abort on就是隐式事务，指从此开始一个事务，一值到整个提交结束都是一个事务。不需要begin tran，两个一起使用，相当与事务嵌套，没有意义。

请不要误导

create table a (x char(3),y int)

set xact_abort on
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
set xact_abort off
go
select * from a

/*
(1 行受影响)
消息 8152，级别 16，状态 14，第 11 行
将截断字符串或二进制数据。

(1 行受影响)

xy
0011
*/

set xact_abort on
begin tran
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
commit
set xact_abort off
go
select * from a

(1 行受影响)
消息 8152，级别 16，状态 14，第 12 行
将截断字符串或二进制数据。

(0 行受影响)

xy
----------

[解决办法]
额，弄错了，，抱歉，这个试开启隐式回滚
而非隐式事务，谢谢的szm341，看来我对set xact_abort on的理解看来有严重错误，多谢指导。
具体范例MSDN上有详细例子

而你的问题回答就应该这样

问题1：set xact_abort on是否一定要跟begin tran/***/commit tran才能回滚？
begin tran。。 commit tran是显示开启事务，若不开启，将只会回滚出错的那条语句

问题2：运行1的表a加了for触发器，插入操作加了事务，2句插入语句都抛出错误，为什么事务没有回滚？
这个是因为raiserror是特殊的，它的处理SQL 进行了特殊处理，相同级别的错误都会产生不同的效果
例如下面例子

Select * from 一个不在的表
if @@error>0
print N'这个没有输出'
go 
raiserror(20522,16,1)
if @@error>0
print N'这个输出了'
go 

所以使用raiserror都应该配合try catch使用，避免类似情况发生。

问题3：运行2的表a加了instead of触发器，插入操作没有加事务，第2句插入语句抛出错误，为什么第1条插入语句也回滚了？
这个问题请参考szm341回复。

问题4：set xact_abort on/begin tran/***/commit tran和begin tran/try***/catch***/commit tran有什么区别？ 
set xact_abort on/begin tran/***/commit tran
对于所有事务后面写了一个
if @error>0 
rollback


简单来说set xact_abort on仅在使用link server时候用，因为这个时候无法使用显示的 rollback。
使用try catch+rollback可控性更高，
[解决办法]
问题1：是的，在触发器中用rollback是可以回滚外部事务，因为触发器是基于事务机制，可以看做

begin tran
insert into a values('001',1)
if exists(select 1 from inserted where y>0)
  raiserror('错误',16,1)--如果在此处rollback的话当然这条语句就回滚了
commit

不过这只是方便你理解，其实并不只会回滚这一条语句，rollback可以将所有嵌套事务回滚至原点，所以有嵌套事务的时候，触发器中rollback要慎用啊

我上面#4、#5举的例子是因为#3所说，xact_abort即是隐式事务而不需要显示的begin tran

从我上面的例子就可以看出他说的是不对的，我也就不多解释了

对例子的理解差不多就是你的意思，下面是联机丛书中的解释，你可以有问题多看看丛书
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。OFF 是默认设置。

set xact_abort off
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
go
select * from a

/*
(1 行受影响)
消息 8152，级别 16，状态 14，第 3 行
将截断字符串或二进制数据。
语句已终止。

(1 行受影响)

(2 行受影响)

xy
0011
0033
*/

set xact_abort on
begin tran
insert into a values('001',1)
--insert into a values('0032',2)
raiserror('错误',16,1)
insert into a values('003',3)
commit
set xact_abort off
go
select * from a

/*
(1 行受影响)
消息 50000，级别 16，状态 1，第 5 行
错误

(1 行受影响)

(2 行受影响)

xy
0011
0033
*/

这两个例子，1说明xact_abort选项起到了批处理语句是否因错误中断的作用（对比#4第一个例子）
2说明自定义的错误提示只作为一个提示，就如print一样，并没有导致执行 Transact-SQL 语句产生运行时错误，但是raiserror仍然会响应try catch，你如果一定要回滚的话可以用try catch来捕获这个错误提示，手动显示回滚
[解决办法]
SET XACT_ABORT ON 是用来保证事务回滚的一致性
举例：
SET XACT_ABORT OFF 
BEGIN TRAN
update... table1 ...
update... table2 ...
update... table3 ...
COMMIT TRAN

如果在update table2是事务中断，这时就会出现table1 update成功，table2,table3没有update这种事务的部分提交。

TRY,CATCH是sql server从2005开始增加的新语法，用来捕获sql 语句的异常用的，格式如下：
BEGIN TRY
...
END TRY
BEGIN CATCH
...
END CATCH

[解决办法]
嗯，是的，一般的事务中，尤其像你这种写自定义提示的，的确用try catch比较好，这样就可以忽略xact_abort，但是在分布式事务中要求必须开启xact_abort选项

查看更多 下一篇