Geeks With Blogs
Brian Biales because blogging is just the easiest way to remember things

Do you write stored procedures that might be used by others?  And those others may or may not have already started a transaction?  And your SP does several things, but if any of them fail, you have to undo them all and return with a code indicating it failed?

Well, I have written such code, and it wasn’t working right until I finally figured out how to handle the case when we are already in a transaction, as well as the case where the caller did not start a transaction.  When a problem occurred, my “ROLLBACK TRANSACTION” would roll back not just my nested transaction, but the caller’s transaction as well.  So when I tested the procedure stand-alone, it seemed to work fine, but when others used it, it would cause a problem if it had to rollback.  When something went wrong in my procedure, their entire transaction was rolled back.  This was not appreciated.

Now, I knew one could "nest" transactions, but the technical documentation was very confusing.  And I still have not found the approach below documented anywhere.  So here is a very brief description of how I got it to work, I hope you find this helpful.

My example is a stored procedure that must figure out on its own if the caller has started a transaction or not.  This can be done in SQL Server by checking the @@TRANCOUNT value.  If no BEGIN TRANSACTION has occurred yet, this will have a value of 0.  Any number greater than zero means that a transaction is in progress.  If there is no current transaction, my SP begins a transaction. But if a transaction is already in progress, my SP uses SAVE TRANSACTION and gives it a name.  SAVE TRANSACTION creates a “save point”.  Note that creating a save point has no effect on @@TRANCOUNT. 

So my SP starts with something like this:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves:

IF @startingTranCount = 0
    COMMIT TRANSACTION

And finally, to roll back just your changes so far:

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION

Here is some code that you can try that will demonstrate how the save points work inside a transaction.

This sample code creates a temporary table, then executes selects and updates, documenting what is going on, then deletes the temporary table.

if running in SQL Management Studio, set Query Results to: Text for best readability of the results.

-- Create a temporary table to test with, we'll drop it at the end.
CREATE TABLE #ATable(
    [Column_A] [varchar](5) NULL
) ON [PRIMARY]

GO
SET NOCOUNT ON
-- Ensure just one row - delete all rows, add one
DELETE #ATable
-- Insert just one row
INSERT INTO #ATable VALUES('000')

SELECT 'Before TRANSACTION starts, value in table is: ' AS Note, * FROM #ATable

SELECT @@trancount AS CurrentTrancount
--insert into a values ('abc')
UPDATE #ATable SET Column_A = 'abc'
SELECT 'UPDATED without a TRANSACTION, value in table is: ' AS Note, * FROM #ATable
BEGIN TRANSACTION
SELECT 'BEGIN TRANSACTION, trancount is now ' AS Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '123'
SELECT 'Row updated inside TRANSACTION, value in table is: ' AS Note, * FROM #ATable
SAVE TRANSACTION MySavepoint
SELECT 'Save point MySavepoint created, transaction count now:' as Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '456'
SELECT 'Updated after MySavepoint created, value in table is: ' AS Note, * FROM #ATable
SAVE TRANSACTION point2
SELECT 'Save point point2 created, transaction count now:' as Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '789'
SELECT 'Updated after point2 savepoint created, value in table is: ' AS Note, * FROM #ATable
ROLLBACK TRANSACTION point2
SELECT 'Just rolled back savepoint "point2", value in table is: ' AS Note, * FROM #ATable
ROLLBACK TRANSACTION MySavepoint
SELECT 'Just rolled back savepoint "MySavepoint", value in table is: ' AS Note, * FROM #ATable
SELECT 'Both save points were rolled back, transaction count still:' as Note, @@TRANCOUNT AS TranCount
ROLLBACK TRANSACTION
SELECT 'Just rolled back the entire transaction..., value in table is: ' AS Note, * FROM #ATable

DROP TABLE #ATable

The output should look like this:

Note                                           Column_A
---------------------------------------------- --------
Before TRANSACTION starts, value in table is:  000

CurrentTrancount
----------------
0

Note                                               Column_A
-------------------------------------------------- --------
UPDATED without a TRANSACTION, value in table is:  abc

Note                                 TranCount
------------------------------------ -----------
BEGIN TRANSACTION, trancount is now  1

Note                                                Column_A
--------------------------------------------------- --------
Row updated inside TRANSACTION, value in table is:  123

Note                                                   TranCount
------------------------------------------------------ -----------
Save point MySavepoint created, transaction count now: 1

Note                                                   Column_A
------------------------------------------------------ --------
Updated after MySavepoint created, value in table is:  456

Note                                              TranCount
------------------------------------------------- -----------
Save point point2 created, transaction count now: 1

Note                                                        Column_A
----------------------------------------------------------- --------
Updated after point2 savepoint created, value in table is:  789

Note                                                     Column_A
-------------------------------------------------------- --------
Just rolled back savepoint "point2", value in table is:  456

Note                                                          Column_A
------------------------------------------------------------- --------
Just rolled back savepoint "MySavepoint", value in table is:  123

Note                                                        TranCount
----------------------------------------------------------- -----------
Both save points were rolled back, transaction count still: 1

Note                                                            Column_A
--------------------------------------------------------------- --------
Just rolled back the entire transaction..., value in table is:  abc

Posted on Thursday, March 15, 2012 4:27 PM SQL Server , T-SQL | Back to top


Comments on this post: How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server

# re: How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server
Requesting Gravatar...
usefull ,THanksz sir
Left by ganeshg on Sep 02, 2015 2:52 AM

# re: How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server
Requesting Gravatar...
Excellent demonstration of rolling back a limited scope of work.
Left by Lawrence M. Smith on Aug 22, 2016 2:07 PM

# re: How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server
Requesting Gravatar...
You could use a transaction name (the same as the savepoint) so the rollback is not dependent on @startingtrancount.

Btw, good example. :)
Left by Rollback could be better on Jun 16, 2017 10:39 AM

# re: How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server
Requesting Gravatar...
I do this, it works whether the caller started the transaction or not and code is simpler. I took out the XACT_STATE() code to make the concept easier to see.

begin transaction -- increments trancount or starts new tran
save transaction @savepoint -- my "ground zero" point
begin try
-- do something

commit; -- either decrements trancount or commits.
end try

begin catch
rollback transaction @savepoint;
commit; -- undo our begin transaction call
end catch

Now, in production I add a check for if the transaction is commitable.

begin catch
if XACT_STATE() = 1
begin
rollback transaction @savepoint;
commit; -- undo our begin transaction call
throw
end
else
throw
end catch


Going even further, I made change the throw statement to pre-append call stack and parameter values.

set @ErrorMsg = 'MyProc(put my params here); '+IsNull(ERROR_MESSAGE(), '')+IsNull('; in '+ERROR_PROCEDURE(), '')+IsNull(' line '+ERROR_LINE(), '')
throw 51000, @ErrorMsg, 1;
Left by Bill Egge on Sep 05, 2017 10:43 PM

Your comment:
 (will show your gravatar)


Copyright © Brian Biales | Powered by: GeeksWithBlogs.net