Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused.
There is a subset of stored procedures that preserve state on failure. That is, if the procedure raises an error, it anyway can be committed safely. This is done with save transaction.
Now, my initial understanding was that the savepoint name you set for the transaction is local to the stored procedure that uses it. And in a nested scenario, if the inner stored procedure saves under the same name, it is actually a different name, and all is fine.
To make sure this understanding of mine was correct, I set up a test case:
create table dbo.[footest] (
v varchar(50) NULL
);
go
insert into dbo.footest(v) values ('Nothing'); go
create procedure dbo.[foo_test_tran_inner]
as
begin
set nocount on;
declare @foo int;
begin tran;
update dbo.footest set v = 'Inner, before savepoint';
save tran the_constant_name;
begin try
update dbo.footest
set v = 'Inner, after savepoint';
set @foo = 1/0;
end try
begin catch
rollback tran the_constant_name;
end catch;
commit tran;
set @foo = 1/0;
return 0;
end;
go
create procedure dbo.[foo_test_tran_outer]
as
begin
set nocount on;
begin tran;
update dbo.footest set v = 'Outer, before savepoint';
save tran the_constant_name;
begin try
update dbo.footest
set v = 'Outer, after savepoint';
exec dbo.foo_test_tran_inner;
end try
begin catch
rollback tran the_constant_name;
end catch;
commit tran;
return 0;
end;
go
begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;
This yields "Outer, before savepoint". Which means, the savepoint names were local to the procedure and were properly rolled back in a nested scenario.
In production, those state-preserving stored procedures have exactly this schema. There is much more code in them, but if you remove it, only leaving saves, rollbacks and commits, it will be exactly what is shown above.
But. It didn't work in production. Instead, each nested savepoint with same name appeared to overwrite a previous savepoint, made from the calling procedure. And when the outermost code after receiving an exception executed a commit (believing the inner rollbacks are properly done), the database was left in a half-screwed state. If applied to the example above, that would mean the select returns Inner, before savepoint.
I stepped through every step in the production procedure with a debugger, and confirmed that execution properly went through all expected save tran a_name and rollback tran a_name.
To fix the problem in production, I replaced this:
save tran the_constant_name;
...
rollback tran the_constant_name;
with this:
declare @savepoint varchar(32) = replace(newid(), '-', '');
save tran @savepoint;
...
rollback tran @savepoint;
and that fixed it immediately.
So what gives? Is the savepoint name local to the stored procedure or not?
If it is, then why the production code did what it did, and was successfully fixed as shown?
If it is not, then why the test example above does what it does?
Best Answer
No they aren't local to a stored procedure.
If you run the following in a database with simple recovery model
It gives these results
The
[Savepoint Name]recorded in the transaction log does not contain any unique identifier related to stored procedure.If you remove the
rollback tran the_constant_name;fromfoo_test_tran_innerthen the result of the final select changes toInner, before savepointshowing that therollback tran the_constant_name;executed fromfoo_test_tran_outerjust rolls back to the most recent (unrolled back) savepoint of that name and takes no account of stored procedure nesting.