回滚嵌套事务的内部事务 [英] Rollback the inner transaction of nested transaction

问题描述

假设我在 sql server 2008 中有以下 sql 语句:

suppose I have following sql statement in sql server 2008:

BEGIN TRANSACTION    
SqlStatement1    
EXEC sp1    
SqlStatement3
COMMIT TRANSACTION

sp1的代码

BEGIN TRANSACTION
SqlStatement2
ROLLBACK TRANSACTION

我的问题是:SqlStatement3 真的执行了吗?

My question is: Is SqlStatement3 actually executed?

推荐答案

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

You can use transaction savepoints. sp1 can use a pattern like the one described in Error Handling and Nested Transactions:

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 中完成的工作回滚，但保持包含的事务处于活动状态.

Such a pattern allow for the work done in sp1 to rollback, but keep the encompassing transaction active.

这篇关于回滚嵌套事务的内部事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！