CODEWITHSUNDEEP
sql-servertransactionssql-server-2014
Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Rollback transaction from called stored procedure

I have a simple scenario: logger procedure and main procedure from which logger is called. I am trying to rollback transaction inside logger which is started in main, but getting errors. I am not sure why. Here are the two procs and the error message I receive:

CREATE PROCEDURE spLogger
AS
BEGIN
    IF @@TRANCOUNT > 0
    BEGIN
       PRINT @@TRANCOUNT
       ROLLBACK
    END
END
GO

CREATE PROCEDURE spCaller
AS
BEGIN
    BEGIN TRY
       BEGIN TRANSACTION
          RAISERROR('', 16, 1)
       COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       EXEC spLogger
    END CATCH
END
GO

EXEC spCaller

1 Msg 266, Level 16, State 2, Procedure spLogger, Line 15 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Upvotes: 1

Views: 4235

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28938

keeping aside all xact_abort stuff,i see no reason why you should get the error.So did some research and here are the observations

----This works

alter PROCEDURE spCaller
AS
BEGIN
    BEGIN TRY
       BEGIN TRANSACTION
          RAISERROR('', 16, 1)
       COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
     rollback 

    END CATCH
END
GO

---Again this works,took the text of sp and kept it in catch block

alter PROCEDURE spCaller
AS
BEGIN
    BEGIN TRY
       BEGIN TRANSACTION
          RAISERROR('', 16, 1)
       COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
     --rollback 
     IF @@TRANCOUNT > 0
    BEGIN
       PRINT @@TRANCOUNT
       ROLLBACK
    END
    END CATCH
END
GO

After some research Found answer by Remus Rusanu here:

If your caller starts a transaction and the calee hits, say, a deadlock (which aborted the transaction), how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with 'business as usual'? The only feasible way is to re-raise an exception, forcing the caller to handle the situation. If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction, only mayhem can ensure (and the error you get is the way the engine tries to protect itself).

In your case,you are getting the error only when using a stored proc and trying to raise the error ,since a stored proc starts a seperate data context.The error you are getting may be SQL way of telling that this wont work.

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

1) The error message is clear: number of active TXs at the end of SP should be the same as number of active TXs at the beginning.

So, when at execution of dbo.spLogger begins the number of active TXs (@@TRANCOUNT) is 1 if we execute within this SP the ROLLBACK statement this'll cancel ALL active TXs and @@TRANCOUNT becomes 0 -> error/exception

2) If you want just to avoid writing IF @@TRANCOUNT ... ROLLBACK within every CATCH block of every user SP then don't it. I would call dbo.spLogger within CATCH block after ROLLBACK.

3) If I have to call SPs from other SP using TXs then I would use following template (source: Rusanu's blog)

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;

        throw;
    end catch   
end

with few small changes:

a) SET XACT_ABORT ON

b) I would call dbo.spLogger within CATCH block only when there is @@TRANCOUNT = 0:

IF @@TRANCOUNT = 0
BEGIN
    EXEC dbo.spLogger ... params ...
END
THROW -- or RAISERROR(@message, 16, @xstate)

Why ? Because if dbo.spLogger SP will insert rows into a dbo.DbException table when one TX is active then in case of ROLLBACK SQL Server will have to ROLLBACL also these rows.

Example: 

SP1 -call-> SP2 -call-> SP3
                         |err/ex -> CATCH & RAISERROR (no full ROLLBACK)
              <-----------
              |err/ex -> CATCH & RAISERROR (no full ROLLBACK)
 <-------------
|err/ex -> CATCH & FULL ROLLBACK & spLogger

4) Update

CREATE PROC TestTx
AS
BEGIN
    BEGIN TRAN -- B
    ROLLBACK -- C
END 
-- D
GO

-- Test
BEGIN TRAN -- A - @@TRANCOUNT = 1
EXEC dbo.TestTx 
    /*
    Number of active TXs (@@TRANCOUNT) at the begining of SP is 1
    B - @@TRANCOUNT = 2
    C - @@TRANCOUNT = 0
    D - Execution of SP ends. SQL Server checks & generate an err/ex
        Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
    */
COMMIT -- E - Because @@TRANCOUNT is 0 this statement generates

another err/ex The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. -- End of Test

5) See autonomous transactions: it requires SQL2008+.

An Autonomous transaction is essentially a nested transaction where the inner transaction is not affected by the state of the outer transaction. In other words, you can leave the context of current transaction (outer transaction) and call another transaction (autonomous transaction). Once you finish work in the autonomous transaction, you can come back to continue on within current transaction. What is done in the autonomous transaction is truly DONE and won’t be changed no matter what happens to the outer transaction.

Upvotes: 3

Related Questions