April 27, 2008 at 1:05 pm
Can somebody please, give me a description of nesting transactions in mssql ?
Thanks
Rabani
April 28, 2008 at 8:16 am
First, please post in the appropriate forum for your question. This has been moved.
Second, a nesting transaction is where you start one transaction and then start a second within it.
Begin transaction 1
update xxx ...
begin transaction 2
delete yy
if @@error 0
rollback transaction 2
else
commit transaction 2
insert zzzz
....
if @@error 0
rollbackup
else
commit
You can start a second transaction and commit it without committing the entire outer transaction (or roll it back).
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 28, 2008 at 9:23 am
Keep in mind that the inner transaction won't actually commit until the outer transaction commits. So - even if the inner transaction finishes, if the outer transaction fails and rolls back, the inner transaction is rolled back/never committed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 12:13 pm
There is a little twist using nested transactions that can catch you. When you commit a transaction, you commit only the current level. When you rollback a transaction, you roll back all levels.
begin tran; -- tran 1
select @@TranCount; -- result = 1
begin tran; -- tran 2
select @@TranCount; -- result = 2
...
begin tran; -- tran 3
select @@TranCount; -- result = 3
...
-- Here's where it gets tricky
if ... begin
commit tran; -- commits only tran level 3
select @@TranCount; -- result = 2
end;
else begin
rollback tran; -- rolls back all trans, not just 3
select @@TranCount; -- result = 0
end;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
February 3, 2010 at 4:07 am
Does that mean that nested transactions has no use what so ever?
February 3, 2010 at 11:09 am
No use whatsoever? I wouldn't say that. I suppose the designers' idea was that every level could reach the end where it would decide either to commit or raise an exception. The exception would be passed along all the way to the top level, where the one and only COMMIT statement would then be executed. A commit would allow the preceding level to proceed until the point where it would also decide to commit at its level or raise an exception and so on. (if good then commit else raise)
So all the error generation would be at the nested levels and the error handling would be at the top level.
This may or may not be the best system that could have been designed, but as long as it is consistently followed, it seems to work fairly well. After all, in the real world, we so rarely need to go more than 2 or 3 levels down.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply