SQL Server - Nested transactions in a stored procedure

Question

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.

Leia

Answer 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

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

Compression

@MichaelRice some further reading on what we've asked for in SQL Server, but has yet to be provided: connect.microsoft.com/SQLServer/feedback/details/296870/… and connect.microsoft.com/SQLServer/feedback/details/324569/… - if you want this functionality, vote and more importantly state your use case. – Compression

Answer 2

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.

Pretonic

Answer 3

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.

Catamount

Answer 4

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

Safelight

Answer 5

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

Isolation

Answer 6

Every stored procedure must end with the same transaction count with which it entered. If the count does not match, SQL Server will issue error 266, "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing."
If a stored procedure does not initiate the outermost transaction, it should not issue a ROLLBACK.

If a nested procedure begin a new transaction; but if it detects the need to roll back and the @@TRANSACTION value is greater than 1, it raises an error, returns an error message to the caller via out parameter or return value, and issues a COMMIT instead of a ROLLBACK.

CREATE PROCEDURE [dbo].[Pinner]
    -- Add the parameters for the stored procedure here
    @ErrorMessage   varchar(max) out
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    begin tran
    begin try
        throw 51000, 'error occured', 1

        commit tran
        set @ErrorMessage = ''
    end try
    begin catch
            set @ErrorMessage = ERROR_MESSAGE();
            if @@TRANCOUNT = 1 
                rollback tran
            if @@TRANCOUNT > 1 
                commit tran
    end catch
END

---

create PROCEDURE [dbo].[Pouter] 
    -- Add the parameters for the stored procedure here
    @ErrorMessage   varchar(max) out
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    begin tran
    begin try


        EXECUTE [dbo].[Pinner] 
           @ErrorMessage OUTPUT
        
        if @ErrorMessage <> '' begin 
            throw 51000, @ErrorMessage, 1
        end

        commit tran
        set @ErrorMessage = ''
    end try
    begin catch
            set @ErrorMessage = ERROR_MESSAGE();

            if @@TRANCOUNT = 1 
                rollback tran
            if @@TRANCOUNT > 1 
                commit tran
    end catch
END

---

DECLARE @ErrorMessage varchar(max)

EXEC    [dbo].[Pouter]
        @ErrorMessage = @ErrorMessage OUTPUT

SELECT  @ErrorMessage as N'@ErrorMessage'

https://www.codemag.com/article/0305111/handling-sql-server-errors-in-nested-procedures

Ratcliffe

Answer 7

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

Sergo