回滚嵌套事务的内部事务

Question

假设我有以下SQL语句在SQL Server 2008中：

BEGIN TRANSACTION    
SqlStatement1    
EXEC sp1    
SqlStatement3
COMMIT TRANSACTION

sp1的代码

BEGIN TRANSACTION
SqlStatement2
ROLLBACK TRANSACTION

我的问题是：是否实际执行了 SqlStatement3？

- Niyoko

Answer 1

SQL Server并不真正支持嵌套事务。一次只能有一个事务。

这个事务有一个基本的嵌套事务计数器@@TRANCOUNT。每执行一次begin transaction，计数器就加一；每执行一次commit transaction，计数器就减一。只有当计数器降至0时，最后一个提交commit才会真正提交整个事务。

rollback transaction会撤销整个事务并清空@@TRANCOUNT。

在您的情况下，有趣的结果是SqlStatement3在一个事务之外运行！ 最终的commit将引发“COMMIT TRANSACTION请求没有相应的BEGIN TRANSACTION”异常，但SqlStatement3的影响是永久性的。

例如：

create table #t (col1 int)
insert #t (col1) values (1)
BEGIN TRANSACTION
update #t set col1 = 2 -- This gets rolled back
BEGIN TRANSACTION
update #t set col1 = 3 -- This gets rolled back too
ROLLBACK TRANSACTION
update #t set col1 = 4 -- This is run OUTSIDE a transaction!
COMMIT TRANSACTION -- Throws error
select col1 from #t

打印输出4。真的，就是这样。

- Andomar

---

这个回答的想法是正确的，但是例子并没有证明什么，因为表变量不受回滚的影响。如果您删除最后一个UPDATE语句，则该行的最终值将为3，而不是1。将表变量更改为临时表将使示例有效。 - Endemic

@Endemic：谢谢，你说得对！我已经更新了答案。 - Andomar

Answer 2

你可以使用事务保存点。sp1 可以使用类似于 错误处理和嵌套事务中描述的模式：

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

这种模式允许在sp1中完成的工作进行回滚，但保持包含事务处于活动状态。

- Remus Rusanu

---

问题在于，ROLLBACK TRANSACTION USP...不会减少@@TranCount。您必须在回滚后调用commit。否则，如果想要提交外部事务（如果内部事务的错误不影响其他更改），就没有机会进行提交。 - Kirsten

Answer 3

可以使用嵌套事务。为了仅回滚内部事务，可以使用保存点并回滚至保存点。如果内部事务不确定是否嵌套，可以使用if语句来判断是否设置保存点以及是回滚还是回滚到保存点：

BEGIN TRAN

    DECLARE @WILL_BE_NESTED_TRANSACTION BIT = CASE WHEN (@@TRANCOUNT > 0) THEN 1 ELSE 0 END
    IF @WILL_BE_NESTED_TRANSACTION = 1
        SAVE TRAN tran_save
    BEGIN TRAN
        -- do stuff

    IF @WILL_BE_NESTED_TRANSACTION = 1
        ROLLBACK TRAN tran_save
    ELSE
        ROLLBACK

ROLLBACK

- JvS

Answer 4

回滚事务会回滚所有的事务。该语句仍然会被执行-请尝试此操作。 http://msdn.microsoft.com/en-us/library/ms181299(v=sql.100).aspx

create table #t (i int)
insert #t values (1)  -- t contains (1)

begin tran
    update #t set i = i +1
    select * from #t  -- t contains (2)
    begin tran
        update #t set i = i +1 
        select * from #t -- t contains (3)
    rollback tran  -- transaction is rolled back

select * from #t -- t contains (1)
update #t set i = i +1
select * from #t -- t contains (2)
commit    -- error occurs
select * from #t -- t contains (2)
drop table #t

- podiluska

---

不，答案是肯定的。该语句将被执行，但提交操作会抛出一个错误。 - podiluska

因此，我可以得出结论：我不能在存储过程中放置 ROLLBACK TRANSACTION，因为该存储过程可能会被其他语句调用。 - Niyoko

你应该检查@@trancount并为你的事务命名。 - podiluska

如果我把我的事务命名为“error occurs in inner rollback”会出现一个有趣的结果.. :D 所以看来我必须检查@@trancount。 - Niyoko

Answer 5

提交内部事务被 SQL Server 数据库引擎忽略。事务的提交或回滚取决于外部最大事务结束时所采取的操作。如果外部事务被提交，则内嵌事务也将被提交。如果外部事务被回滚，则不管内部事务是否已经提交，所有内部事务都将被回滚。

在 Microsoft TechNet 中嵌套事务

- user2928048