Fun with Transactions - Part I

  • Comments posted to this topic are about the item Fun with Transactions - Part I

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

    Follow me on
    Twitter: @sqltwins

  • Nice question - thanks.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • learned something new today... thanks...:-)

  • Good Question thanks 🙂

  • My guess is right. 🙂

    Nice Question.

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.

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

  • da-zero (9/14/2010)


    Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.

    It is really good to know that the question helped in your preparations. Best of luck for your exam!

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

    Follow me on
    Twitter: @sqltwins

  • Oops, I got it wrong.

    In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.

    Would have made more sense if they had employed a slightly different keyword such as e.g.

    ROLLBACK TRAN ALL

    or

    ROLLBACK TRANSACTIONS

    Just my view 🙂

  • phil.wood 94423 (9/14/2010)


    Oops, I got it wrong.

    In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.

    Would have made more sense if they had employed a slightly different keyword such as e.g.

    ROLLBACK TRAN ALL

    or

    ROLLBACK TRANSACTIONS

    Just my view 🙂

    Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.

    If you really want full nesting, you can use checkpointing in your transactions and use named transactions.

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

  • That'll teach me to shoot before I aim.

    Thanks

  • I was wrong answer: "Outer transaction is still open....rolling back... ".

    I learned that my wrong answer can be obtained with a SAVEPOINT:

    CREATE TABLE MyTable (MyId INT IDENTITY (1,1),

    MyCity NVARCHAR(50))

    BEGIN TRANSACTION OuterTran

    INSERT INTO MyTable VALUES ('Boston')

    BEGIN TRANSACTION InnerTran

    SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT

    INSERT INTO MyTable VALUES ('London')

    ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN

    IF (@@TRANCOUNT = 0)

    BEGIN

    PRINT 'All transactions were rolled back'

    END

    ELSE

    BEGIN

    PRINT 'Outer transaction is still open....rolling back...'

    ROLLBACK TRANSACTION OuterTran

    END

    DROP TABLE MyTable

  • Learned something new today:)

  • Good question - almost got me. If i hadn't have reread the code and noticed the way the rollback was written. Proves that rereading code is good.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • LUCAB (9/14/2010)


    I was wrong answer: "Outer transaction is still open....rolling back... ".

    I learned that my wrong answer can be obtained with a SAVEPOINT:

    SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT

    And while the actual question didn't teach me anything new, I hadn't heard of Transaction savepoints... that's really neat.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Always good to have a refresher on transaction basics! Thanks

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

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