Post reply

XACT_STATE = 1 in a transaction error?

  • May 16, 2011 at 10:54 am

    #237415

    I'm trying to make sure my procedures are more robust have started looking at the XACT_STATE for TRY...CATCH in a stored procedure. I'm not sure I understand how I could start a transaction, have the transaction error out (and move to the CATCH stage) and still be in a committable state. Can someone give me an example where that would happen?

    Donalith

  • May 16, 2011 at 11:24 am

    #1325320

    Is this any help?

    http://msdn.microsoft.com/en-us/library/ms189797.aspx

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 16, 2011 at 11:28 am

    #1325324

    Thanks, Corgi, I've read that. I'm trying to get an example of where the transaction failed so it moved from the TRY to the CATCH phrase but was still in an XACT_STATE of 1 and still commitable.

  • May 16, 2011 at 11:53 am

    #1325349

    Unless XactAbort is on, pretty much any non-fatal error will do that (pk violation, data type conversion, constraint violations, etc)

    Interestingly enough, I have a blog post on this being published tomorrow.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 16, 2011 at 11:57 am

    #1325352

    I look forward to reading that, Gail!

    So as a rough idea.. unless XACT_ABORT is ON (which I have set as I don't want non-constrained data in my tables) then SQL treats is more like a warning and you can commit it if you're determined rather than having an XACT_STATE = -1 being set on constraint failures?

    Donalith

  • May 16, 2011 at 12:14 pm

    #1325366

    Kinda, but not a warning. Statement-terminating error (as opposed to batch-terminating or connection-terminating)

    What you're seeing there is pretty much what the default behaviour without a try-catch is. Don't roll transactions back for non-fatal (statement-terminating) errors.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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