SQL Server - Nested transactions in a stored proce

2019-04-06 17:51发布

Lets say this is the situation:

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

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

Thanks.

6条回答
Lonely孤独者°
2楼-- · 2019-04-06 18:10

In nested transactions, if any of the inner transations rolls back, all its outer transaction will rollback.

查看更多
戒情不戒烟
3楼-- · 2019-04-06 18:15
USE [DemoProject]
GO

/****** Object:  StoredProcedure [dbo].[Customers_CRUD]    Script Date: 11-Jan-17 2:57:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Customers_CRUD]
      @Action VARCHAR(10)
      ,@BId INT = NULL
      ,@Username VARCHAR(50) = NULL
      ,@Provincename VARCHAR(50) = NULL
      ,@Cityname VARCHAR(50) = NULL
      ,@Number VARCHAR(50) = NULL
      ,@Name VARCHAR(50) = NULL
      ,@ContentType VARCHAR(50) = NULL
      ,@Data VARBINARY(MAX) = NULL

AS
BEGIN
      SET NOCOUNT ON;

      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT BId , Username,Provincename,Cityname,Number,Name,ContentType, Data
            FROM tblbooking
      END

      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO tblbooking(Username,Provincename,Cityname,Number,Name,ContentType, Data)
            VALUES (@Username ,@Provincename ,@Cityname ,@Number ,@Name ,@ContentType ,@Data)
      END

      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE tblbooking
            SET Username = @Username,Provincename = @Provincename,Cityname = @Cityname,Number = @Number,Name = @Name,ContentType = @ContentType,Data = @Data
            WHERE BId = @BId
      END

      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM tblbooking
            WHERE BId = @BId
      END
END

GO
查看更多
4楼-- · 2019-04-06 18:17

It is possible for the work done by SP2 to be rolled back and not loose the work done by SP1. But for this to happen, you must write your stored procedures using a very specific pattern, as described in Exception 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

Not all errors are recoverable, there are a number of error conditions that a transaction cannot recover from, the most obvious example being deadlock (your are notified of the deadlock exception after the transaction has already rolled back). Both SP1 and SP@ have to be written using this pattern. If you have a rogue SP, or you want to simple leverage existing stored procedures that nilly-willy issue ROLLBACK statements then your cause is lost.

查看更多
霸刀☆藐视天下
5楼-- · 2019-04-06 18:20

Here is a quick and dirty way to nest transactions in stored procedures (using the code from Aaron's answer) that can be useful sometimes. It uses a default parameter to indicate to the inner procedure if it is a nested call, and returns a success/fail result to the outer procedure.

CREATE PROCEDURE dbo.sp2
    @trip BIT,
    @nested BIT = 0
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON

    IF @nested = 0 BEGIN TRAN

    PRINT @@TRANCOUNT

    IF @trip = 1
    BEGIN
        IF @nested = 0 ROLLBACK
        RETURN 1
    END
    ELSE
    BEGIN   
        IF @nested = 0 COMMIT
    END

    PRINT @@TRANCOUNT
    RETURN 0
END
GO

The outer procedure checks the success/fail an rolls back the transaction if appropriate.

CREATE PROCEDURE dbo.sp1
    @trip BIT
AS
BEGIN
    DECLARE @result INT
    SET NOCOUNT, XACT_ABORT ON

    BEGIN TRAN

    PRINT @@TRANCOUNT

    BEGIN TRY
        EXEC @result = dbo.sp2 @trip = @trip, @nested = 1
        IF @result <> 0
        BEGIN
            ROLLBACK
            RETURN 1
        END
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH

    PRINT @@TRANCOUNT

    COMMIT

    PRINT @@TRANCOUNT
    RETURN 0
END
GO
查看更多
贼婆χ
6楼-- · 2019-04-06 18:22

If SP2 rolls back the transaction, SP1 will rollback as well.

See: http://msdn.microsoft.com/en-US/library/ms187844(v=sql.105).aspx for details.

查看更多
家丑人穷心不美
7楼-- · 2019-04-06 18:28

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

Inner proc:

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

Outer proc:

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;

Results:

1
2
1
1
0

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

EXEC dbo.sp1 @trip = 1;

Results:

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

查看更多
登录 后发表回答