SQL Server-存储过程中的嵌套事务 [英] SQL Server - Nested transactions in a stored procedure

问题描述

我们可以说是这种情况:

Lets say this is the situation:

  [Stored Proc 1]
  BEGIN
     BEGIN TRANSACTION
       ...
            exec sp 2   
     COMMIT
  END

现在，如果SP 2(由于任何原因而回滚)，SP 1是否会提交或回滚或引发异常?

Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception?

谢谢.

推荐答案

SQL Server中没有自治事务.您可能会看到@@TRANCOUNT增加到1以上，但回滚会影响整个过程.

There are no autonomous transactions in SQL Server. You may see @@TRANCOUNT increase beyond 1, but a rollback affects the whole thing.

编辑要求指向文档.不知道明确记录此主题的主题，但是我可以在实际操作中向您展示.

EDIT asked to point to documentation. Don't know of the topic that documents this explicitly, but I can show it to you in action.

USE tempdb;
GO

内部过程:

CREATE PROCEDURE dbo.sp2
    @trip BIT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    PRINT @@TRANCOUNT;

    IF @trip = 1
    BEGIN
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN   
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
    END

    PRINT @@TRANCOUNT;
END
GO

外部进程:

CREATE PROCEDURE dbo.sp1
    @trip BIT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    PRINT @@TRANCOUNT;

    BEGIN TRY
        EXEC dbo.sp2 @trip = @trip;
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH

    PRINT @@TRANCOUNT;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

    PRINT @@TRANCOUNT;
END
GO

现在让我们称它为一切":

So now let's call it and let everything commit:

EXEC dbo.sp1 @trip = 0;

结果:

1
2
1
1
0

1
2
1
1
0

现在让我们调用它并回滚内部过程:

Now let's call it and roll back the inner procedure:

EXEC dbo.sp1 @trip = 1;

结果:

1
2
0<-请注意，此处的回滚会同时回滚两个
EXECUTE之后的交易计数表明数字不匹配 BEGIN和COMMIT语句.上一个计数= 1，当前计数= 0.
0
0

1
2
0 <-- notice that a rollback here rolled back both
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
0
0

这篇关于SQL Server-存储过程中的嵌套事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！