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 Wednesday, September 22, 2010 8:59 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:47 AM
Points: 1,435, Visits: 1,846
Comments posted to this topic are about the item Fun with Transactions - Part IV

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #991679
Posted Wednesday, September 22, 2010 9:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #991680
Posted Wednesday, September 22, 2010 10:08 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Good one It's easy to get lost in this type of questions
Post #991692
Posted Wednesday, September 22, 2010 10:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thanks for the question, I appreciate the effort you have put forward in helping everyone to better understand transactions.
Post #991699
Posted Wednesday, September 22, 2010 10:35 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:47 AM
Points: 1,435, Visits: 1,846
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #991702
Posted Wednesday, September 22, 2010 11:43 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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.
Post #991733
Posted Thursday, September 23, 2010 12:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 13, 2011 8:48 AM
Points: 208, Visits: 87
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
Post #991762
Posted Thursday, September 23, 2010 1:03 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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 #991764
Posted Thursday, September 23, 2010 1:06 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:47 AM
Points: 1,435, Visits: 1,846
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #991765
Posted Thursday, September 23, 2010 1:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
Nice question. Although the first answer is also a bit valid, as both transactions are indeed rollbacked. (due to the outer transaction)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #991785
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse