SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Transactions - Part IV


Fun with Transactions - Part IV

Author
Message
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 1300
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.
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 377
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

Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7644 Visits: 2629
Good question. Ahh......the subtleties of SQL Server. Thanks.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5609 Visits: 1619
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.
Bradley Deem
Bradley Deem
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 1248
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64033 Visits: 18570
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

deepak.a
deepak.a
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1282 Visits: 863
Thanks for the nice Question Smile
tilew-948340
tilew-948340
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 2437
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
Hardy21
Hardy21
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1399
Good series of questions Nakul. Thanks

Thanks
amit_adarsh
amit_adarsh
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 169
Perfect explanation and example .
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search