Transactions 2

  • Comments posted to this topic are about the item Transactions 2

    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 question !!!

  • this question look very familiar......

    too bad i got it wrong...again.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Very nice question, definately learned something.

    Got it wrong, as I say "begin transaction" I immediately thought "rollback everything". Apparently not 🙂

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

  • Good question with a couple of learning points.

    1. By default SQL does not abort the entire transaction if it encounters a statment-level failure. In this case, the primary key violation is a statement level failure and hence, only the statement terminated, not the entire transaction

    If the requirement is to cause a failure of the entire transaction (i.e. abort), then the following SET option needs to be set to ON:

    SET XACT_ABORT ON

    Setting XACT_ABORT to ON will cause SQL Server to abort the transaction even if it encounteres the statement level failure.

    2. Referring one of my blog posts, row constructors (introduced in SQL Server 2008) process the entire batch at once. Hence, had this example used row constructors, the correct answer would have been 0 rows affected (Reference post: http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx)

    Thank-you!

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

    Follow me on
    Twitter: @sqltwins

  • the answer is 0 rows when xact_abort is set to on

    the answer is 2 rows when xact_abort is set to off

  • john.straver (5/23/2012)


    the answer is 0 rows when xact_abort is set to on

    the answer is 2 rows when xact_abort is set to off

    By default, SET XACT_ABORT is OFF.

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

    Follow me on
    Twitter: @sqltwins

  • So that means that when xact_abort is set to off then

    BEGIN TRANSACTION

    ...

    COMMIT TRANSACTION

    does not do as expected.

    What's the default setting on a fresh install of SQL?

  • Neil Thomas (5/23/2012)


    So that means that when xact_abort is set to off then

    BEGIN TRANSACTION

    ...

    COMMIT TRANSACTION

    does not do as expected.

    What's the default setting on a fresh install of SQL?

    Ah, an additional comment has been added whilst I wrote this.

    That's a bit of a gotcha isn't it?

  • Nakul Vachhrajani (5/22/2012)


    Good question with a couple of learning points.

    1. By default SQL does not abort the entire transaction if it encounters a statment-level failure. In this case, the primary key violation is a statement level failure and hence, only the statement terminated, not the entire transaction

    If the requirement is to cause a failure of the entire transaction (i.e. abort), then the following SET option needs to be set to ON:

    SET XACT_ABORT ON

    Setting XACT_ABORT to ON will cause SQL Server to abort the transaction even if it encounteres the statement level failure.

    2. Referring one of my blog posts, row constructors (introduced in SQL Server 2008) process the entire batch at once. Hence, had this example used row constructors, the correct answer would have been 0 rows affected (Reference post: http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx)

    Thank-you!

    The question is interesting. I first thought the select would give 0 rows, but then i remind myself that XACT_ABORT is set to OFF by default.

    Thank you

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Good question. Got it wrong.

    M&M

  • Good question, but not sure about the explanation, which talks about implied transactions even though this one is explicit . The issue surely is that statement failure doesn't cause an automatic abort/rollback unless you've coded one.

  • Neil Thomas (5/23/2012)


    So that means that when xact_abort is set to off then

    BEGIN TRANSACTION

    ...

    COMMIT TRANSACTION

    does not do as expected.

    What's the default setting on a fresh install of SQL?

    I guess that depends on the expectation. The code in this QotD contains no error-handling and an unconditional COMMIT statement.

    For handling SQL Server errors, it is important to realize that various errors have various effects on running code. Off the top of my head, the potential effects are:

    * Compile-time error - the entire batch is not executed; including statements BEFORE the one that causes the error (because the batch is compiled as a whole before execution starts). Execution halts.

    * Statement-aborting - running statement is rolled back; transaction is left intact; execution continues.

    * Transaction-invalidating - running statement is rolled back; transaction is marked invalid (meaning you'll get an error if you try to commit); execution continues.

    * Transaction-aborting - running statement and open transactions (if any) rolled back; execution continues. You'll get an error if you execute a commit or rollback statement after this.

    * Batch aborting - running statement is rolled back. I don't know if transaction is rolled back or left open (sorry). Rest of batch is not executed; execution continues at next batch.

    * Connection aborting - running statement and open transactions rolled back; connection dropped (so all execution halts). This only happens in the case of some very severe errors (like drive failures or so).

    For a very complete discussion on this subject, read http://www.sommarskog.se/error_handling_2005.html.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Good question, caught me out! As one of the other posters said, I was railroaded by BEGIN TRANSACTION - thought the entire batch would be rolled back - apparently not. Nice one.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Good question.

    Unfortunately the explanation is all about implicit transactions, while the question is about an explicit transaction. So the explanation is not exactly helpful!

    Tom

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

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