Very useful SET XACT_ABORT TIP#94

 

SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.

Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.

Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.

Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be a cause of locking problem.

In such situation  XACT_ABORT option is helpful.

By default XACT_ABORT option is off you can on it by following statement

   SET XACT_ABORT ON;

Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.

Let me explain with below example

Suppose I wrote following statement “Notice here XACT ABORT is OFF

image

Now , when I run the above statements I will get following result in result window

image

And in the message window we will get following result

image

What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query

image

Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??

image

It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.

 

I appreciate your inputs on this valuable topic.

Thanks

RJ!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s