Home » Blog » How to rollback in procedures

How to rollback in procedures

2012-01-23 - General

Transaction Management

The question where in the call stack a transaction should be managed, is similar to the one where errors should be handled. There are several strong reasons to push the transaction management as high up the stack as possible. The most obvious reason is the lack of support for nested transactions in SQL Server.

SQL Server does allow to nest BEGIN TRANSACTION ... COMMIT blocks within each other. However, only the outermost layer actually opens and closes a transaction. In the inner layers SQL Server just maintains a counter; only the outermost COMMIT has any effect on the data.

With that structure in place, SQL Server has no way to do a partial rollback, so if a rollback is requested, everything is rolled back to the beginning of the outer most transaction.

If you have code, that does some data changes followed by a procedure call followed by other data changes, all within a transaction, and the procedure decides to execute a rollback, the rest of the data changes after the procedure call will execute outside of any transaction. Among other catastrophies this can cause inconsistencies in your data and it will prevent another rollback later.

SQL Server realizes this to be a mayor issue. It raises an error anytime the transaction count before and after the execution of a procedure do not match. You can try this out with this code snippet:

[sql] IF OBJECT_ID('dbo.DoRollback') IS NOT NULL DROP PROCEDURE dbo.DoRollback;
GO
CREATE PROCEDURE dbo.DoRollback
AS
BEGIN
BEGIN TRAN
ROLLBACK;
RETURN 0;
END;
GO

RAISERROR('Calling dbo.DoRollback outside of transaction',0,1)WITH NOWAIT;
EXEC dbo.DoRollback; -- No Error

RAISERROR('Calling dbo.DoRollback inside of transaction',0,1)WITH NOWAIT;
BEGIN TRAN;
EXEC dbo.DoRollback; -- Error
[/sql]

Executing the procedure after starting a transaction causes an error like this:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

That error however does not prevent execution of additional code. The following example demonstrates that by calling the above procedure within another procedure:

[sql] IF OBJECT_ID('dbo.CallDoRollback') IS NOT NULL DROP PROCEDURE dbo.CallDoRollback;
GO
CREATE PROCEDURE dbo.CallDoRollback
AS
BEGIN
BEGIN TRAN;
EXEC dbo.DoRollback;
RAISERROR('--Inside dbo.CallDoRollback after calling dbo.DoRollback',0,1)WITH NOWAIT;
IF(@@TRANCOUNT>0)ROLLBACK;
RETURN 0;
END;
GO

RAISERROR('Calling dbo.CallDoRollback',0,1)WITH NOWAIT;
EXEC dbo.CallDoRollback;
[/sql]

So the conclusion here is, that a stored procedure should never execute a rollback. Instead it should raise an error to signal to the outer layer that a situation occurred that requires some mediation like a rollback.

Partial Rollbacks

Every once in while you need to code a procedure that needs to undo its own actions independent of the circumstances it was called in. That can be achieved with a partial rollback.

While SQL Server does not support nested transactions, it allows you to set a save-point within an open transaction. After setting a safe-point you can then rollback all changes that occurred after it. The DoCorrectRollback procedure demonstrates how you can use that functionality in your procedures.

[sql] IF OBJECT_ID('dbo.DoCorrectRollback') IS NOT NULL DROP PROCEDURE dbo.DoCorrectRollback;
GO
CREATE PROCEDURE dbo.DoCorrectRollback
AS
BEGIN
DECLARE @ErrorStatus INT; SET @ErrorStatus = 0;
DECLARE @TranName VARCHAR(32);SET @TranName = REPLACE((CAST(NEWID() AS VARCHAR(36))),'-','');

BEGIN TRAN;
RAISERROR('Setting savepoint: %s',0,1,@TranName)WITH NOWAIT;
SAVE TRAN @TranName;

BEGIN TRY
EXEC dbo.DoError;
END TRY
BEGIN CATCH
DECLARE @msg NVARCHAR(MAX);SET @msg = ERROR_MESSAGE();
RAISERROR('Caught this error: %s',0,1,@msg)WITH NOWAIT;
SET @ErrorStatus = -1
END CATCH;

IF(@ErrorStatus != 0)
BEGIN
RAISERROR('Rolling back to savepoint: %s',0,1,@TranName)WITH NOWAIT;
ROLLBACK TRANSACTION @TranName;
END;

COMMIT;

RETURN 0;
END;
GO

RAISERROR('Calling dbo.DoCorrectRollback outside of transaction',0,1)WITH NOWAIT;
EXEC dbo.DoCorrectRollback;

RAISERROR('Calling dbo.DoCorrectRollback inside of transaction',0,1)WITH NOWAIT;
BEGIN TRAN
EXEC dbo.DoCorrectRollback;
COMMIT;
GO
[/sql]

The procedure first executes a BEGIN TRANSACTION to make sure that there is an active transaction. (Remember, this does not have any effect inside a preexisting transaction.) Next, the procedure sets a save-point. A save-point requires a unique name that can be up to 32 characters long. The procedure uses the NEWID() function to get this name. NEWID() returns 36 characters of which 4 are a '-', so by removing them we end up with the required 32 characters.

After this transaction setup section the actual code is executed inside a TRY...CATCH block. If an error is thrown, the error handling code causes a ROLLBACK to be executed. The ROLLBACK TRANSACTION statement takes the save-point name as parameter, which causes the rollback to undo only actions that happened after the save-point was set - the beginning of the procedure in our case.

This ROLLBACK to the previously set save-point does not change the open transaction counter at all, so before the procedure returns it needs to execute a commit to bring the transaction counter back to the state it was in at the beginning of the procedure. This step has to be taken independently of the ROLLBACK being executed or not. It should therefore be the last statement of the procedure.

This allows the procedure to be called inside and outside of a transaction without causing adverse effects by executing a rollback.

Limitations

SQL Server allows to set several save-points within one active transaction as long as they have unique names. If a name is reused the existing save-point is moved to the current position and no error is thrown, so be sure to use unique names as shown in the example.

Another limitation is, that save-points cannot be used in distributed transactions spanning more than one SQL Server instance. If you try to set a save-point in a distributed transaction an error is thrown.

Lastly, the example given does not notify the outer layers about the existence of a problem. This should certainly be part of the design of your procedures. There are several ways to achieve this, for example by raising an error after the rollback step that contains the reason for the rollback.