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\ 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