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

问题描述

假设情况是这样的:

  [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 要求指向文档.不知道哪个主题明确记录了这一点，但我可以在行动中向您展示.

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屋！