sqlserver 事务详解

仔细研究了下，发现sql server里面的explicit transaction（显示事务）还是有点复杂的。以下是有些总结：Commit transaction 会提交所有嵌套的transaction修改。但是如果嵌套的transaction里面有rollback tran to save point, 那么save point之后的部分会revert掉。delete from dbo.num

黑贝是条狗

3324人浏览 · 2022-05-09 18:29:01

黑贝是条狗 · 2022-05-09 18:29:01 发布

仔细研究了下，发现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，得到的结果和上面一样。但是有一点特殊的地方在与，执行存储过程结束的时候会比较开始执行存储过程的@@trancount和结束时候@@trancount的值，如果不一样，Sqlserver会给出一个消息像“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

如果在存储过程里面做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