Transaction handling in sql server

Question

Transaction handling in sql server

vsamantha35

SSChampion

Points: 11529
More actions
November 7, 2019 at 9:31 am

#3695827

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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: 11529 More actions · Answer 2

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: 148809 More actions · Answer 3

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: 11529 More actions · Answer 4

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: 148809 More actions · Answer 5

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: 11529 More actions · Answer 6

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: 148809 More actions · Answer 7

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: 100949 More actions · Answer 8

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) A socialist is someone who will give you the shirt off *someone else's* back.