Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fun with Transactions - Part IV Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
p0peye (9/23/2010)
Please correct me if i am wrong...

My understanding is, you wont be able to commit or rollback inner transaction separately as the outer transaction will override with its commit/rollback command. If this is true, why do we need to use nested transactions or in which scenario nested transactions can be used?

let me know if my question is not clear


_________
Andrew


Having the ability to nest transactions is necessary for stored procedures that might call other stored procedures. Each one might have it's own transaction embedded in the code. Otherwise, it's not really necessary to nest them like in the example.

This was a good question. I debated between the first and last choices and ultimately picked the wrong one. I wasn't sure because I knew that the inner commits are ignored and only the outer transaction really means anything. If you commit the outer transaction, all inner transactions are also committed. If you rollback the outer transaction, all inner transactions are also rolled back (regardless if you committed the inner transaction or not).

This one had a rollback in the inner transaction so I also ignored that one and assumed everything would be rolled back anyway -- WRONG! Causes an error because you cannot rollback inner transactions.
Post #991804
Posted Thursday, September 23, 2010 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,017, Visits: 369
thanks, i was looking for an explaination wherein we save the inner transaction...

tommyh (9/23/2010)
p0peye (9/23/2010)
Please correct me if i am wrong...

My understanding is, you wont be able to commit or rollback inner transaction separately as the outer transaction will override with its commit/rollback command. If this is true, why do we need to use nested transactions or in which scenario nested transactions can be used?

let me know if my question is not clear


_________
Andrew


Now this is a bit out off my league. But i would say that your partly correct. You cant commit an inner transaction and then later rollback the outer one. You can however using "save transaction" rollback a part and then commit the rest. Like this
 
create table #TransTest (i integer)

begin tran OuterTran
insert into #TransTest (i) values (1)

save tran InnerTran
insert into #TransTest (i) values (2)
rollback tran InnerTran

commit tran OuterTran

select * from #TransTest

drop table #TransTest

Here only one value is inserted into the table the second row is rolled back.

/T
Post #991838
Posted Thursday, September 23, 2010 6:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
Good question. Ahh......the subtleties of SQL Server. Thanks.
Post #991969
Posted Thursday, September 23, 2010 7:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
looking at the options, I thought option one would be the right answer. Since both inner and outer rollback, query would return zero rows.

then executed it and got right answer.

Thanks for all questions on rollback series .


SQL DBA.
Post #991999
Posted Thursday, September 23, 2010 8:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:33 AM
Points: 554, Visits: 1,196
Great series of questions. Although, I think the correct answer is actually option 1 and option 3. IE

The following error is raised "Cannot roll back InnerTran. No transaction or savepoint of that name was found." and All transactions are rolled back and an empty recordset is returned.


Now, if you had SET XACT ABORT ON then it would have just been option 3 as the batch would abort and the select would not be executed. Lucky, I figured you were looking for the error so I chose option 3 and got it right.
Post #992054
Posted Thursday, September 23, 2010 11:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 21,657, Visits: 15,325
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #992209
Posted Friday, September 24, 2010 3:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Thanks for the nice Question :)
Post #992592
Posted Friday, September 24, 2010 7:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
thank you for the question and thanks to all the people that gived more examples to explain it because I have read the microsoft explanation but my comprehension of it was somewhat wrong

Post #993178
Posted Sunday, September 26, 2010 11:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 1,117, Visits: 1,375
Good series of questions Nakul. Thanks

Thanks
Post #993435
Posted Monday, November 29, 2010 10:15 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:25 PM
Points: 698, Visits: 158
Perfect explanation and example .

Post #1027727
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse