October 10, 2015 at 9:45 pm
Please help me to understand whats wrong with my approach?
I have a SP which have named transaction
for ex: create sp sp_1
begin try
begin tran inner
--- some insert/update commands
commit tran inner
end try
begin catch
rollback tran inner
end catch
I am calling this sp in a sp which will be another sub-sp which will be called in a transaction(the reason why I am using named transaction )
create sp sp_2
begin try
begin tran maintran
exec sp_1
commit tran maintran
end try
begin catch
rollback tran maintran
end catch
I am getting below mentioned error
Cannot roll back inner. No transaction or savepoint of that name was found.
Thanks in Advance
October 12, 2015 at 2:39 am
There's no such thing as a nested transaction. It's just syntatical lies.
Naming a transaction does nothing at all. It's basically a form of documentation, it's ignored almost everywhere.
The first begin transaction starts the transaction. The second does nothing other than increment @@Trancount. A rollback anywhere ALWAYS rolls back to the outermost transaction. A commit will only actually commit the transaction when @@trancount decrements to 0, otherwise all it does is decrement @@trancount.
You need to figure out a way to write that with a single transaction only. Start your transactions only at one level, commit/rollback only at one level.
Nested transactions are a lie.
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
October 12, 2015 at 2:42 am
Just as Gail said.
Here is some further reading.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/"> http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply