set xact_abort on 回滚问题

Question

本帖最后由 drsheldoncooper 于 2013-03-22 23:31:10 编辑

Answer 1

问题1：是的，xact_abor只是个选项，要配合显示事务才有效果
问题2：你定义的错误只能被try catch捕获处理，不能引发tran的回滚，就如你定义的只是个提示，并没有真正意义上事务的错误
问题3：如2所说，其实并没有引发回滚，而是你定义的instead of触发器代替了insert操作，只给出了一个提示
问题4：加上try catch的话就可以处理你定义的那种错误提示了，可以转到catch语句块中进行显示rollback操作

Answer 2

问题1：set xact_abort on是否一定要跟begin tran/***/commit tran才能回滚？
     set xact_abort on就是隐式事务，指从此开始一个事务，一值到整个提交结束都是一个事务。不需要begin tran，两个一起使用，相当与事务嵌套，没有意义。
     简单来说就是     set xact_abort on
     insert into a values('001',1)
     insert into a values('002',2)等价于     begin tran
     insert into a values('001',1)
     insert into a values('002',2)
     commit tran问题2：运行1的表a加了for触发器，插入操作加了事务，2句插入语句都抛出错误，为什么事务没有回滚？
      你可以试试
      begin tran
insert into a values('001',1)
commit tran
      
      你会发现没有发生回滚
然后再试试begin try
begin tran
insert into a values('001',1)
commit tran
end try
begin catchend catchOK插入语句发生了回滚
再试试set xact_abort on
begin try
insert into a values('001',1)
end try
begin catch

end catch
set xact_abort off你会发现你得到了想要的效果问题3：运行2的表a加了instead of触发器，插入操作没有加事务，第2句插入语句抛出错误，为什么第1条插入语句也回滚了？
请注意set xact_abort on的定义，，，一旦发生错误回滚整个事务，整个事务包含第一条语句。
而且，，你的语句2中没有加try，一条也回滚不了啦问题4：set xact_abort on/begin tran/***/commit tran和begin tran/try***/catch***/commit tran有什么区别？ 
try***/catch***  用于捕捉错误
set xact_abort on 等价于  begin tran

Answer 3

请不要误导
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 行受影响)x y
001 1
*/
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 行受影响)x y
----------

Answer 4

再补个例子
set xact_abort off
begin tran
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
commit
go
select * from a/*
(1 行受影响)
消息 8152，级别 16，状态 14，第 12 行
将截断字符串或二进制数据。
语句已终止。(1 行受影响)(2 行受影响)x y
001 1
003 3
*/

Answer 5

谢谢解答，学习了。如果for触发器在错误后增加rollback，是不是就能代替try/catch回滚整个事务了？如下：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>1)
begin
  raiserror('错误',16,1)
  rollback--增加回滚
end
go
set xact_abort on
begin tran
insert into a values('001',1)
insert into a values('002',2)--出错
commit tran
go
--------结果------------
x          y
----------------另外，#4、#5的例子可以将set xact_abort on理解为错误中断吗？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 行受影响)
 
x    y
001    1
*/第2条出错，所以第3条也没有执行。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 行受影响)
 
x    y
---------第2条出错，所以整个事务中断回滚。set xact_abort off
begin tran
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
commit
go
select * from a
 
/*
(1 行受影响)
消息 8152，级别 16，状态 14，第 12 行
将截断字符串或二进制数据。
语句已终止。
 
(1 行受影响)
 
(2 行受影响)
 
x    y
001    1
003    3
*/因为set xact_abort off，所以第2条出错后没有中断，继续执行第3条。
上面的理解都正确吗？
但是为什么第2个例子insert into a values('0032',2)能回滚事务，而触发器的raiserror()不能回滚事务呢？

Answer 6

谢谢解答，学习了。如果for触发器在错误后增加rollback，是不是就能代替try/catch回滚整个事务了？如下：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>1)
begin
  raiserror('错误',16,1)
  rollback--增加回滚
end
go
set xact_abort on
begin tran
insert into a values('001',1)
insert into a values('002',2)--出错
commit tran
go
--------结果------------
x          y
----------------另外，#4、#5的例子可以将set xact_abort on理解为错误中断吗？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 行受影响)
  
x    y
001    1
*/理解：第2条出错，所以第3条也没有执行。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 行受影响)
  
x    y
---------理解：第2条出错，所以整个事务中断。set xact_abort off
begin tran
insert into a values('001',1)
insert into a values('0032',2)
insert into a values('003',3)
commit
go
select * from a
  
/*
(1 行受影响)
消息 8152，级别 16，状态 14，第 12 行
将截断字符串或二进制数据。
语句已终止。
  
(1 行受影响)
  
(2 行受影响)
  
x    y
001    1
003    3
*/理解：因为set xact_abort off，所以第2条出错后没有中断，继续执行第3条。上面的理解都正确吗？但是为什么第2个例子insert into a values('0032',2)能回滚事务，而触发器的raiserror()不能回滚事务呢？set xact_abort on响应什么类型的错误而回滚事务呢？

Answer 7

额，弄错了，，抱歉，这个试开启隐式回滚
而非隐式事务，谢谢的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可控性更高，

Answer 8

问题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 行受影响)x y
001 1
003 3
*/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 行受影响)x y
001 1
003 3
*/
这两个例子，1说明xact_abort选项起到了批处理语句是否因错误中断的作用（对比#4第一个例子）
2说明自定义的错误提示只作为一个提示，就如print一样，并没有导致执行 Transact-SQL 语句产生运行时错误，但是raiserror仍然会响应try catch，你如果一定要回滚的话可以用try catch来捕获这个错误提示，手动显示回滚

Answer 9

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

Answer 10

感谢详细解答。try/catch能捕获错误进行回滚，那么是不是就可以省略设置set xact_abort on了呢？

Answer 11

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