Save Transaction: Is the name local to the stored procedure or not? 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?
No they aren't local to a stored procedure.

If you run the following in a database with simple recovery model

checkpoint
begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

select Operation,
       Context,
       [Savepoint Name],
       [Transaction Name] ,
       Description 
from sys.fn_dblog(NULL,NULL)


It gives these results

+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     Operation      | Context  |  Savepoint Name   | Transaction Name |                                                                                               Description                                                                                                |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOP_BEGIN_CKPT     | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_END_CKPT       | LCX_NULL | NULL              | NULL             | log_minRecoveryLsn 00000112:00001ce6:008e;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000 |
| LOP_BEGIN_XACT     | LCX_NULL | NULL              | user_transaction | user_transaction;0x010500000000000515000000007dfcff481d5bed8a729370ee030000                                                                                                                              |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_COMMIT_XACT    | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


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; from foo_test_tran_inner then the result of the final select changes to Inner, before savepoint showing that the rollback tran the_constant_name; executed from foo_test_tran_outer just rolls back to the most recent (unrolled back) savepoint of that name and takes no account of stored procedure nesting.