How to avoid duplicate savepoint names in nested transactions in nested stored procedures?

Question

I have a template that I almost always follow, where if I need to complete the operation in a transaction, I do this:

BEGIN TRANSACTION SAVE TRANSACTION TX -- Stuff IF @error <> 0 ROLLBACK TRANSACTION TX COMMIT TRANSACTION 

This served me well enough in the past, but after several years of using this template (and copying the above code), I suddenly found a flaw that becomes a complete shock.

Quite often, I will have a stored procedure that calls other stored procedures, all of which use the same template. What I discovered (to my value) is that since I use the same savepoint name everywhere, I can get into a situation where my external transaction is partially completed - exactly the opposite of the atomicity that I am trying to achieve .

I put together an example demonstrating the problem. This is a separate batch (without nested stored procedures), and therefore it looks a bit strange because you probably won’t use the same savepoint name twice in the same batch, but my real-world scenario will be too confusing to publish.

 CREATE TABLE Test (test INTEGER NOT NULL) BEGIN TRAN SAVE TRAN TX BEGIN TRAN SAVE TRAN TX INSERT INTO Test(test) VALUES (1) COMMIT TRAN TX BEGIN TRAN SAVE TRAN TX INSERT INTO Test(test) VALUES (2) COMMIT TRAN TX DELETE FROM Test ROLLBACK TRAN TX COMMIT TRAN TX SELECT * FROM Test DROP TABLE Test 

When I do this, it lists one entry with a value of "1". In other words, although I rolled back my external transaction, the record was added to the table.

What happens because ROLLBACK TRANSACTION TX on the external level rolls back to the last SAVE TRANSACTION TX on the internal level. Now, when I write all this, I see the logic of this: the server looks at the log file, considering it as a linear transaction flow; he does not understand nesting / hierarchy implied either by nesting of transactions (or, in my real scenario, by calls of other stored procedures).

So, obviously, I need to start using unique savepoint names instead of blindly using "TX". But ... and this is where I finally got to the bottom - is there a way to do this in copied form so that I can still use the same code all over the world? Can I automatically generate a savepoint name on the fly? Is there a convention or best practice for this kind of thing?

It’s not possible to come up with a unique name each time the transaction is started (one could base it on the name SP or something similar), but I’m worried that there will eventually be a conflict - and you won’t know about it, because instead of calling mistake, it just silently destroys your data ...: - (

Answer 1

Look at the docs: SAVE TRANSACTION (Transact-SQL)

 SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } [ ; ] 

it looks like you can name it based on a variable, so try making your template:

 DECALRE @savepoint_variable varchar(1000) SET @savepoint_variable=OBJECT_NAME(@@PROCID)+'|'+CONVERT(char(23),GETDATE(),121) BEGIN TRANSACTION SAVE TRANSACTION @savepoint_variable -- Stuff IF @error <> 0 BEGIN ROLLBACK TRANSACTION @savepoint_variable END COMMIT TRANSACTION 

when called from different procedures, your @savepoint_variable will have a different local value, and your rollbacks should rollback properly. I entered the current time and time in the name of the save point, because you can use recursion at some point, and if it is a copy pattern template, it is better to handle all cases.

Answer 2

Accept KM's decision.

I prefer to use a GUID, but to create unique savepoint names.

 DECLARE @savepoint AS VARCHAR(36) SET @savepoint = CONVERT(VARCHAR(36), NEWID()) BEGIN TRANSACTION SAVE TRANSACTION @savepoint ... ROLLBACK TRANSACTION @savepoint COMMIT TRANSACTION