Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Ok. That makes sense. They are global and I've learnt that. But why does it work as naively expected in the example? Does the first rollback make it "forget" the savepoint, so that the second rollback rolls back to the previous savepoint of the same name? And if so, how came it didn't happen in production, where number of rollbacks equals number of save trans? –  GSerg Feb 22 '12 at 15:24
    
@GSerg - Once the inner savepoint is rolled back then that inner savepoint is no longer treated as part of the log so it is ignored when the next rollback savepoint is encountered. Presumably in production your stored procedures don't always end up executing rollback tran the_constant_name; or there wouldn't be much point? –  Martin Smith Feb 22 '12 at 15:31
    
That is exactly what I cannot understand. If a rollback was stepped over for some reason, then surely switching to the newid() thing would not help? But it did help, and it's 100% reproducible; if I switch it back to the constant name, it is misbehaving always, and with the random name it is doing the right thing always. –  GSerg Feb 22 '12 at 16:28
    
@GSerg If Proc 1 does save tran S1 then Proc 2 does save tran S1 and exits without issuing a rollback tran S1 then you have an imbalance in that you still have 2 open savepoints of that name so when Proc 1 issues rollback tran S1 it only affects the inner one. If Proc 1 does save tran P1 then Proc 2 does save tran P2 and exits without issuing a rollback tran P2 then Proc 1 issues rollback tran P1 it will rollback to where you want as there is only one savepoint named P1 (which also includes save point P2) –  Martin Smith Feb 22 '12 at 16:38
    
Right. Yes, that makes sense. The code needs another stare which it will get. But then, it's not that big, and in two days I haven't been able to find any possible way for the control flow to not hit both rollbacks, both by executing it in my head and pressing F11 repeatedly. Either someone is a moron or this needs to be properly reproduced and posted to connect. Thanks for your help. –  GSerg Feb 22 '12 at 17:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.