Fun with Transactions - Part IV

Question

Post reply

Fun with Transactions - Part IV

Nakul Vachhrajani

SSChampion

Points: 10283
More actions
September 22, 2010 at 8:59 pm

#240821

Comments posted to this topic are about the item Fun with Transactions - Part IV
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice series of questions -- it will teach a great number of people a lot of what they should/need to know

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

Good one It's easy to get lost in this type of questions

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Thanks for the question, I appreciate the effort you have put forward in helping everyone to better understand transactions.

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 4

Thank-you for the kind words, "UMG Developer" (I am sorry for using your alias), Ron and Dhruvesh.

I have learnt a great deal from the community, and when I was first faced with a situation I highlighted in Part I, I researched it through and came up with this series.

Knowledge is power, and it definitely grows by sharing.

We have a very nice community here at SSC, and I hope to be an active part of this in the time to come.

Once again, thank-you all, and have a very nice day ahead!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

tommyh SSCertifiable Points: 6302 More actions · Answer 5

Good question series. Got owned on the first 2 questions, so decided to read and test a bit on the subject. Now the last 2 questions i have been able to answer correctly. 😀

p0peye SSC Veteran Points: 242 More actions · Answer 6

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

tommyh SSCertifiable Points: 6302 More actions · Answer 7

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

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 8

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

Hello, Andrew.

While this question has been discussed at length in the discussions of the previous questions in this same series (published starting from September 10, 2010); I would say that your understanding is partially right.

You do not trully "commit" the inner transaction as long as you don't commit the outer transaction. However, you can use save points within your transactions and rollback to the chosen point. This is demonstrated in the discussions on the first part of the series.

I would definitely recommend reading those discussions and going through all the parts of the series, and I am sure that all your questions will be answered.

If any remain, please feel free to post them and we will try to help you out as best as we can.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

Nice question. Although the first answer is also a bit valid, as both transactions are indeed rollbacked. (due to the outer transaction)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

cengland0 SSCertifiable Points: 6102 More actions · Answer 10

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 SSCertifiable Points: 7985 More actions · Answer 11

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 SSC-Dedicated Points: 34566 More actions · Answer 12

Good question. Ahh......the subtleties of SQL Server. Thanks.

SanjayAttray SSChampion Points: 13157 More actions · Answer 13

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 SSCrazy Points: 2565 More actions · Answer 14

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.