Fun with Transactions - Part IV

  • 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

  • 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]

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

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

  • 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

  • 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. 😀

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

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

  • 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.

  • 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.

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

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