How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server
- by Brian Biales
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
