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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3314 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4495 Visits: 380
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17450 Visits: 2629
Good question. Ahh......the subtleties of SQL Server. Thanks.
SanjayAttray
SanjayAttray
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1623 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 (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144567 Visits: 18651
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
Learn Extended Events

deepak.a
deepak.a
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2990 Visits: 863
Thanks for the nice Question Smile
tilew-948340
tilew-948340
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1949 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
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: 5570 Visits: 1399
Good series of questions Nakul. Thanks

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

Group: General Forum Members
Points: 994 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