Transaction Save Point (SET XACT_ABORT { ON | OFF })
2015-05-07 16:00 Purplez 阅读(389) 评论(0) 收藏 举报
ref:http://blog.csdn.net/wym3587/article/details/6940630
ref:http://www.cnblogs.com/jiajiayuan/archive/2011/07/13/2105398.html
当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。OFF 是默认设置。
success code:
print @@trancount begin tran SET XACT_ABORT off CREATE TABLE ValueTable ([value] int) save transaction sp1 begin try INSERT INTO ValueTable VALUES(1); --begin try --INSERT INTO ValueTable VALUES('ok'); --end try --begin catch Raiserror('eeee',16,1) --end catch end try begin catch select @@trancount rollback transaction sp1 end catch commit DROP TABLE ValueTable;
failure code because error is so serious :
print @@trancount begin tran SET XACT_ABORT off CREATE TABLE ValueTable ([value] int) save transaction sp1 begin try INSERT INTO ValueTable VALUES(1); begin try INSERT INTO ValueTable VALUES('ok'); end try begin catch Raiserror('eeee',16,1) end catch end try begin catch select @@trancount rollback transaction sp1 end catch commit DROP TABLE ValueTable; Output Message: 0 (1 row(s) affected) (0 row(s) affected) (1 row(s) affected) Msg 3931, Level 16, State 1, Line 19 The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
So, in catch block, we should rollbacck entire tran when(XACT_STATE()) = -1:
BEGIN CATCH -- Test XACT_STATE for 0, 1, or -1. -- If 1, the transaction is committable. -- If -1, the transaction is uncommittable and should -- be rolled back. -- XACT_STATE = 0 means 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 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION; END; END CATCH;