Post reply

Transactions 2

  • May 24, 2012 at 1:07 am

    #1492276

    rmechaber (5/23/2012)

    ...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].

    For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].

    Rich

    An excellent article, that should be compulsory reading for all us SQL developers!

    Thanks for posting Rich.

  • May 24, 2012 at 2:08 am

    #1492298

    Nasty, but great question. Tricked me! 😎

    Michael Gilchrist
    Database Specialist
    There are 10 types of people in the world, those who understand binary and those that don't. 😀

  • May 24, 2012 at 2:24 am

    #1492304

    Nice question and nice conversation as always 🙂

    Thanks

  • May 24, 2012 at 2:48 am

    #1492325

    I have to say that I am astonished that I was not crystal clear on this(I got it wrong), after the years I have programmed against SQL Server. In mitigation I am an application developer and thus less likely to work directly in the way this question operates (i.e. I would control transactions externally in most cases). Still, this is why I keep doing the QOTD to be reminded of my limitations - great discussion which has really clarified the whole tx thing and the reasons behind how it works the way it does, thanks.

  • May 24, 2012 at 2:59 am

    #1492330

    Hugo Kornelis (5/23/2012)

    If you want the application to be outside SQL Server, you should ... even control the transactions from the application.

    That's what we do (MTS in our case). Has its down sides, but allows all the error handling and logging to be done in the application.

  • May 24, 2012 at 3:25 am

    #1492343

    Nice question i got it correct,i encountered a similar experience.I liked the explanation learned something.thank you.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • May 24, 2012 at 6:07 am

    #1492456

    ralph.bacon (5/24/2012)

    rmechaber (5/23/2012)

    ...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].

    For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].

    Rich

    An excellent article, that should be compulsory reading for all us SQL developers!

    Thanks for posting Rich.

    YW, thanks for letting me know you found it useful!

    Rich

  • May 24, 2012 at 11:16 am

    #1492700

    Great question! You'd be surprised to know how many SQL developers think if any error is encountered within a transaction it automatically gets rolled back like when XACT_ABORT is on.

  • May 24, 2012 at 7:38 pm

    #1492906

    I got it right for the wrong reason. Learned something. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • August 9, 2012 at 9:00 am

    #1523859

    Good question.

  • October 8, 2012 at 12:50 am

    #1546570

    wrong answer 🙁

    I learned a lot from transaction questions 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 9, 2013 at 5:06 am

    #1575176

    I answered 1 row.

    Assuming violation is at second row so first row will be xcuted succesffully. One more thing i didn't notice that all three insert statements are inside a transaction.

    But if it is implict transaction and set xact_abort is ON

    Then first row will be inserted sucessfully..

    --
    Dineshbabu
    Desire to learn new things..

Viewing 12 posts - 46 through 56 (of 56 total)

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