Timeline for Save Transaction: Is the name local to the stored procedure or not?
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 22, 2012 at 17:06 | vote | accept | GSerg | ||
| Feb 22, 2012 at 17:06 | comment | added | GSerg | 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. | |
| Feb 22, 2012 at 16:38 | comment | added | Martin Smith |
@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)
|
|
| Feb 22, 2012 at 16:28 | comment | added | GSerg |
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.
|
|
| Feb 22, 2012 at 15:31 | comment | added | Martin Smith |
@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?
|
|
| Feb 22, 2012 at 15:24 | comment | added | GSerg |
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?
|
|
| Feb 22, 2012 at 13:30 | history | edited | Martin Smith | CC BY-SA 3.0 |
added 4162 characters in body
|
| Feb 22, 2012 at 13:22 | history | answered | Martin Smith | CC BY-SA 3.0 |