May 4, 2015 at 4:13 am
I am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
select 1/0
end try
begin catch
rollback transaction t1
end catch
begin try
delete #t3
select 1/0
end try
begin catch
rollback transaction t2
end catch
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
May 4, 2015 at 9:44 am
When you rollback t1, everything done after the SAVE TRAN statement is rolled back.
t2 is after t1. As long as t1 is active, you can rollback to t2, but the minute t1 is rolled back, t2 is gone too.
May 4, 2015 at 10:02 am
A name specified on a rollback MUST match the name given on the outermost begin tran. You have no name on the begin tran, hence naming the rollback throws an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2015 at 12:48 pm
Thanks a lot..I was trying to figure out the reason since long..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply