Post reply

Transactions

  • October 8, 2014 at 9:25 pm

    #74260

    Comments posted to this topic are about the item Transactions

  • October 9, 2014 at 12:21 am

    #1751155

    Nice and easy. Thanks for sharing.

  • October 9, 2014 at 12:46 am

    #1751160

    Great question, thanks.

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

  • October 9, 2014 at 12:49 am

    #1751162

    Koen Verbeeck (10/9/2014)

    Great question, thanks.

  • October 9, 2014 at 1:15 am

    #1751167

    There are no one completely correct answer.

    A1 are the most correct.

    If the answer hade been "The insert succeeds and the update fails BUT when are both rolled back".

    Have I right?

  • October 9, 2014 at 2:48 am

    This was removed by the editor as SPAM

  • October 9, 2014 at 4:35 am

    #1751203

    Stewart "Arturius" Campbell (10/9/2014)

    Good question, thanks Steve

    patrik.ljunggren (10/9/2014)

    There are no one completely correct answer.

    A1 are the most correct.

    If the answer hade been "The insert succeeds and the update fails BUT when are both rolled back".

    Have I right?

    No, there is no error handling (e.g. TRY / CATCH) and there is no rollback statement.

    Therefore, the transaction will begin, execute the INSERT, fail on the UPDATE, then commit.

    +1, that was really a good question, thanx Steve.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • October 9, 2014 at 5:31 am

    #1751223

    Good question, Steve. And the point it illustrates is an important one to understand.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 9, 2014 at 6:02 am

    #1751242

    From the information given, I'm missing why the update fails. We're told that it's a primary key, but that doesn't stop it being updated.

    create table test (id int NOT NULL PRIMARY KEY)

    GO

    INSERT test VALUES (1)

    GO

    UPDATE test SET id=2 WHERE id=1

    GO

    This works without error for me.

    I'd understand it failing if it were an IDENTITY but we weren't told that?

    What am I missing please folks?.

    Thanks for your indulgence.

    Keith

    Doh - forget it, I'd missed that there was already an item with PK of 3. Ignore this (but I'll leave it here in case it helps anyone else as dumb as me!).

  • October 9, 2014 at 6:19 am

    #1751248

    The question assumes that XACT_ABORT is set to OFF. I know that's the default, but it would be worth mentioning in the question or explanation for clarity and completeness.

    John

  • October 9, 2014 at 6:22 am

    #1751251

    Very good question, Steve.

    I learned this the hard way a looooong time ago while preparing some migration scripts.

    ---------------
    Mel. 😎

  • October 9, 2014 at 6:23 am

    #1751252

    I assumed it was a trick so I chose incorrectly despite looking at the right answer.

    It proves that I didn't know the answer as confidently as I should have known.

  • October 9, 2014 at 6:24 am

    #1751254

    Thank you for the post, Steve, very important one.

    TRY/CATCH first choice - or go classic (sql 2000 way, it works) store the error status in the variable on each DML statement and then use IF to check those variables not equal to zero then rollback and commit when both are zero.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • October 9, 2014 at 6:39 am

    #1751263

    Ugh...I new I hadn't had enough coffee yet but answered anyway. I must have looked at it 3 times and never caught the missing Try...Catch.

    Aigle de Guerre!

  • October 9, 2014 at 7:18 am

    #1751277

    Thanks for the question.

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

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