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:
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 GO RAISERROR('Calling dbo.DoRollback inside of transaction',0,1)WITH NOWAIT; BEGIN TRAN; EXEC dbo.DoRollback; -- Error
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:
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;
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.
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.
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; GO RAISERROR('Calling dbo.DoCorrectRollback inside of transaction',0,1)WITH NOWAIT; BEGIN TRAN EXEC dbo.DoCorrectRollback; COMMIT; GO
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.
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.
@jeremyjsimmons Nasty bug in my cms. Have not been able to fix it yet.
I tried this approach and found that whether it works depends on the state of the transaction. Specifically, when the transaction is in an uncommittable state (XACT_STATE() returns -1) it can only be fully rolled back and cannot be rollback to a save point. So the error handling code in a sproc has to do a full rollback when XACT_STATE() returns -1, in which case we'll still get @@TRANCOUNT mismatch message. I am not sure if there is a way that works regardless of the transaction state.
@Wenning Qiu , sadly there is not. Once is transaction has been marked as uncommittable, SQL Server severely restricts what still can be done. I did not add any special handling for that situation, because it really cannot be "handled". You will end up with an error independent of what you do.
Hi Sebastian I really like your idea of only partially rolling back the transaction in the SP. I would like to implement it, however I don't think it will work with some error logging I have implemented. Currently my SPs have the form: CREATE PROCEDURE dbo.SomeSP @Param1 INT, @Parma2 INT AS BEGIN SET NOCOUNT ON DECLARE @err_msg VARCHAR(max) SET @err_msg = '@Param1 = ''' + CONVERT(VARCHAR(10), @Param1) + ', ' + '@Param2 = ''' + CONVERT(VARCHAR(10), @Param2) + '''' BEGIN TRY BEGIN TRANSACTION /* Code to do stuff */ IF @@TRANCOUNT > 0 COMMIT END TRY BEGIN CATCH PRINT ERROR_MESSAGE() IF @@TRANCOUNT > 0 ROLLBACK EXEC sysmaint.Log_InsertError @err_msg END CATCH SET NOCOUNT OFF END The sysmaint.Log_InsertError SP defined this: CREATE PROCEDURE [sysmaint].[Log_InsertError] @err_msg VARCHAR(max) = NULL AS BEGIN INSERT INTO sysmaint.ErrorLog ( ErrorNumber ,ErrorSeverity ,ErrorState ,ErrorProcedure ,ErrorLine ,ErrorMessage ,CustomMessage) VALUES ( ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), @err_msg) SET @err_msg = 'An error occured, please refer to sysmaint.ErrorLog (ErrorLogID = ' + CONVERT(VARCHAR(10), SCOPE_IDENTITY()) + ') for details - ' + ERROR_MESSAGE() RAISERROR(@err_msg, 16, 0) END This means that if an error occurs it is propagated up the stack. The DB also keeps a record with all the extra details such as the line number and the parameters that were used. I've used this technique before and it has turned out to be a real time saver when it comes to debugging a production system. Hence I would like to keep using it. However it does not play nicely with tSQLt. This is the first project I've used tSQLt with and I have to say I've been really loving it. I would however like to extend the tests to cover exception handling but came across the problem caused by that ROLLBACK in my CATCH block. Doing a bit of searching via Google led me to this post which at first seemed to be the answer I was looking for and I was ready to change all my SPs over to using your technique. However I can see a problem. For instance if I have SP1 which in turn calls SP2 and an exception happens in SP2. I can see in most cases I would want the exception that happened in SP2 logged and then propagated up to SP1 which will then propagate the exception up to it's caller. Using the partial rollback on both SPs, the partial rollback in SP1 will remove the log event from SP2. What are your thoughts on the best practice for handling such situations? I can see a couple of options but none of them stand out as the best method.