Transaction handling in sql server

  • vsamantha35

    SSChampion

    Points: 11167

    Hi All,

    Have some questions related to transaction management in sql server.

    1. What is the difference between having SET XACT_ABORT is ON and having BEGIN TRY ... CATCH ROLLBACK;

    2. What if I use only BEGIN TRY ... CATCH ROLLBACK; and do not use XACT_ABORT ON ? Any issues we need to keep in mind?

    3. I see XACT_ABORT is a server level setting and is applied to all databases. Can we set it as a specific database level setting?

    4. Coming to recent issue in our env app team has come up with is, on one of the DEV server, this setting XACT_ABORT setting is OFF at instance level and so ETL batch loads were giving different results, i mean loads were successful. But on Prod this option is XACT_ABORT is ON at instance level, so ETL batch loads are failing ? question is, which is good ? do we need to turn on this option or turn off to allow batch loads to be run successful?

    Thanks,

    Sam

  • John Mitchell-245523

    SSC Guru

    Points: 148745

    If XACT_ABORT is ON, an error will cause the whole transaction to be rolled back; if it's OFF, only the statement being executed will be rolled back.  When XACT_ABORT is OFF, you'll want to use TRY...CATCH blocks to handle errors in explicit transactions.  You'll want to test to see whether the whole transaction is rolled back when XACT_ABORT is ON and you use TRY...CATCH.

    XACT_ABORT is a connection setting, not a server-level setting, although it's true you can set the default value for it in the server properties.

    The answer to whether to set XACT_ABORT ON or OFF depends on how your code handles errors.  I recommend that you include an explicit SET XACT_ABORT ON/OFF statement at the top of all your code, instead of relying on the default which, as you have seen, can vary from server to server.

    John

  • vsamantha35

    SSChampion

    Points: 11167

    John Mitchell-245523 wrote:

    If XACT_ABORT is ON, an error will cause the whole transaction to be rolled back; if it's OFF, only the statement being executed will be rolled back.  When XACT_ABORT is OFF, you'll want to use TRY...CATCH blocks to handle errors in explicit transactions.  You'll want to test to see whether the whole transaction is rolled back when XACT_ABORT is ON and you use TRY...CATCH.

    XACT_ABORT is a connection setting, not a server-level setting, although it's true you can set the default value for it in the server properties.

    The answer to whether to set XACT_ABORT ON or OFF depends on how your code handles errors.  I recommend that you include an explicit SET XACT_ABORT ON/OFF statement at the top of all your code, instead of relying on the default which, as you have seen, can vary from server to server.

    John

     

    XACT_ABORT is ON on prod so whole transaction is getting rollbacked. on non-prod envs, it is off, so it is allowing loads to continue.

    server settings

  • John Mitchell-245523

    SSC Guru

    Points: 148745

    OK, are you telling me that in case I'm interested, or do you have a follow-up question?  Note that it says "Default connection options" in that dialog box.  As I said before , it's better to code explicitly than to rely on server defaults.

    John

  • vsamantha35

    SSChampion

    Points: 11167

    I am just showing my observation. I saw it in server level connection settings. I didn't find any setting database level setting.

    Any idea, on how to set this option from .net connection string?

     

  • John Mitchell-245523

    SSC Guru

    Points: 148745

    This is a good reference for connection strings.  If you can't find what you're looking for there, just include the SET statement in your code.  Probably best to do that in any case, since you don't know who's going to fiddle with your connection string.

    John

  • vsamantha35

    SSChampion

    Points: 11167

    John, I have a question. In case of app timeout. if this xabort setting is OFF and assume that app has started a load txn(bulk INSERT), and in between due to some reason (say network issue etc..) app got timed out, connection got broken and the transaction will be kept open at sql server level. In that case, what will happen to that transaction ? will sql server automatically identify and rollabck such transactions or dba has to kill those transactions ? If DBA has to KILL such open txn spids, how to identify that so and so spid(s) belong to timedout txn or so? is there any particular column in sp_whoisactive or sp_who or sysprocesses which says that?

  • John Mitchell-245523

    SSC Guru

    Points: 148745

    I'd guess that if XACT_ABORT is OFF then only the statement that's running at the moment of the timeout will be rolled back.  Whether that rollback occurs gracefully and expeditiously will depend on a number of factors - you'll want to test all foreseeable scenarios before you go live.  I think rolled back transactions will have ROLLBACK or something similar in the Status column of sp_who until the rollback is complete.

    John

  • ScottPletcher

    SSC Guru

    Points: 98427

    I'd suggest always using SET XACT_ABORT ON.

    (1) Otherwise, you can't be sure which part(s) of a transaction completed and which didn't.  Sometimes the entire transaction will be failed even with XACT_ABORT OFF, sometimes it won't.

    (2) Any distributed transaction (that actually attempts a data modification) with another SQL Server instance will require XACT_ABORT ON anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 9 posts - 1 through 9 (of 9 total)

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