sql server transaction

渡人自渡

　　仔细研究了下，发现sql server里面的explicit transaction还是有点复杂的。以下是有些总结：

　　·         Commit transaction 会提交所有嵌套的transaction修改。但是如果嵌套的transaction里面有rollback tran to save point, 那么save point之后的部分会revert掉。
　　delete from dbo.numbertable
　　begin tran out1
　　     insert into dbo.numbertable values(1)
　　     insert into dbo.numbertable values(2)
　　     
　　     begin tran inn1
　　          insert into dbo.numbertable values(3)
　　          insert into dbo.numbertable values(4)
　　     save tran inn1SavePoint
　　          insert into dbo.numbertable values(5)
　　     rollback tran inn1SavePoint
　　     commit tran inn1
　　commit tran out1
　　
　　·         @@TRANCOUNT可以用来记录当前session transaction的个数，对于嵌套的transaction来讲，每次begin transaction都让它加一，每次commit tran都会让它减一。所以在语句里面可以通过select @@TRANCOUNT 来检查当前是否在一个transaction里面。如果当前@@TRANCOUNT为0，那调用commit还是rollback都会出现语句错误。在嵌套的transaction里面，rollback是很特殊的，它会直接把@@TRANCOUNT设置为0。
　　begin tran
　　begin tran
　　begin tran
　　print @@trancount
　　rollback tran
　　print @@trancount
　　
　　·         对于嵌套的transaction来讲，rollback的写法是很特殊。如果嵌套，rollback transaction后面是不能带transaction的name的，要带也只能是最外面的transaction的name。Rollback只会抛弃所有嵌套transaction在rollback语句之前的修改。Rollback之后的更新依然提交就去了，原因在于：rollback之后，@@trancount为0，那么rollback之后的语句就不属于explicit transaction, 属于autocmmit transaction了，自动提交。
　　delete from dbo.numbertable
　　begin tran t1
　　     insert into dbo.numbertable values(1)
　　     
　　     begin tran t2
　　          insert into dbo.numbertable values(2)
　　     rollback tran
　　     print 'after rollback in innert transaction, the transaction count is: '+cast(@@trancount, varchar(5))
　　     insert into dbo.numbertable values(3)
　　--commit tran
　　select * from dbo.numbertable
　　
　　·         存储过程里面也可以begin transaction，如果调用的地方也begin transaction,那么这种情况也属于嵌套transaction，如果在存储过程里面rollback，得到的结果和上面一样。但是有一点特殊的地方在与，执行存储过程结束的时候会比较开始执行sp的@@trancount和结束时候@@trancount的值，如果不一样，它会给出一个消息像“Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.”这个给出的消息并不会影响其后的执行。
　　CREATE PROCEDURE [dbo].[AddNumber]        
　　AS
　　BEGIN
　　     begin tran
　　          insert into dbo.numbertable values(1)
　　          insert into dbo.numbertable values(2)
　　          insert into dbo.numbertable values(3)
　　     rollback tran
　　END
　　delete from dbo.numbertable
　　begin tran out1
　　exec dbo.addnumber
　　print @@trancount
　　insert into dbo.numbertable values(3)
　　select * from dbo.numbertable
　　
　　·         如果在sp里面rollback了，那到外满做commit， 或者rollback都是没有效果并且出错了，因为嵌套的transaction内部transaction一旦调用了rollback，@@trancount就为0了，在外满commit，rollback直接出错。比如如下sp，我想像在最外面rollback，那就出错了，因为sp里面语句rollback了。表里面始终会插入值3。
　　delete from dbo.numbertable
　　begin tran out1
　　exec dbo.addnumber
　　print @@trancount
　　insert into dbo.numbertable values(3)
　　rollback tran out1
　　select * from dbo.numbertable
　　
　　·         所有对于嵌套的transaction来讲，如果内部transaction一旦rollback，就会给外部的transaction留下一个大坑。为了解决这个为题，有两种解决方案：
　　1.       在外部的transaction里面检查@@trancount，如果这个值跟你代码begin tran的可以一致，那说明内部transaction没有rollback，那可以继续commit或者rollback。
　　delete from dbo.numbertable
　　begin tran t1
　　     insert into dbo.numbertable values(1)
　　     
　　     begin transaction t2
　　          insert into dbo.numbertable values(2)
　　     rollback tran
　　
　　     if @@trancount = 1
　　     begin
　　          insert into dbo.numbertable values(3)
　　          commit tran
　　     end
　　2.       在所有的内部transaction里面，只能commit，不能rollback。如果必须rollback，那怎么办？save point就可以派上用场了。比如sp改成这样子：
　　ALTER PROCEDURE [dbo].[AddNumber]         
　　AS
　　BEGIN
　　     begin tran
　　     save tran pp
　　          insert into dbo.numbertable values(1)
　　          insert into dbo.numbertable values(2)
　　          insert into dbo.numbertable values(3)
　　     rollback tran pp
　　     commit tran
　　END
　　
　　begin tran out1
　　exec dbo.addnumber
　　print @@trancount
　　insert into dbo.numbertable values(3)
　　commit tran out1