When we need to use SET XACT_ABORT ON in Sql server procedures?

Question

When we need to use SET XACT_ABORT ON in Sql server procedures?

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
September 9, 2014 at 5:30 am

#289941

hi ALl,
I have the following doubts.
1. In my procedure i dont have any transactions still can i use SET XACT_ABORT ON in my procedure?
2. In my procedure we dont have TRY and CATCH blocks then can we use SET XACT_ABORT ON ?

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

kbhanu15 (9/9/2014)
hi ALl,
I have the following doubts.
1. In my procedure i dont have any transactions still can i use SET XACT_ABORT ON in my procedure?

The procedure is either in an explicit or an implicit transaction, if the transaction isn't set explicitly by BEGIN TRANSACTION, it will be set automatically as an implicit transaction. XACT_ABORT will work in both cases, but note that it is not supported by all providers.

2. In my procedure we dont have TRY and CATCH blocks then can we use SET XACT_ABORT ON ?

Try/Catch will trap the exception for handling, if not used the whole transaction is rolled back.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Eirikur Eiriksson (9/9/2014)
The procedure is either in an explicit or an implicit transaction, if the transaction isn't set explicitly by BEGIN TRANSACTION, it will be set automatically as an implicit transaction. XACT_ABORT will work in both cases, but note that it is not supported by all providers.

No.

If there isn't an explicit BEGIN TRANSACTION, then SQL runs in auto-commit mode, committing every data modification as soon as it's complete. In that case, there's little point for XACT_ABORT.

Implicit transactions is Oracle's default, when the first statement starts a transaction and that transaction remains open until explicitly committed. SQL won't do that unless Implicit_Transactions has been turned on in that session.

Try/Catch will trap the exception for handling, if not used the whole transaction is rolled back.

The whole transaction will be rolled back only if there is an explicit transaction (which the OP said there isn't) and xact_abort is on. Otherwise just the statement that threw the error will be rolled back and the rest will complete and commit.

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

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 3

thanks for you reply but if possible can u please explain me with example.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

In short, if you don't have explicit transactions (BEGIN TRANSACTION ...), there's no reason to have xact abort on. Xact abort defines whether an explicit or implicit transaction rolls back when there's an error. If you are in auto-commit (the default), there's no transactions to be automatically rolled back.

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

GilaMonster (9/9/2014)
In short, if you don't have explicit transactions (BEGIN TRANSACTION ...), there's no reason to have xact abort on. Xact abort defines whether an explicit or implicit transaction rolls back when there's an error. If you are in auto-commit (the default), there's no transactions to be automatically rolled back.

I was partially wrong, with auto-commit and XACT_ABORT the transaction is committed until the error occurs, without it, only the statement causing the error is rolled back, previous and subsequent statements are successful.

Hence the remainder of the batch is rolled back (more precisely does not execute) when an error is hit with those settings.

So Gail, it does make a difference;-)

😎

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 6

Sorry still i am not clear on my question ?

My question is

1. When we will use SET XACT_ABORT ON in my procedures ?

a)if we have transactions in our proc then can we use SET XACT_ABORT ON ?

or

b)if we dont have transactions in our proc then can we use SET XACT_ABORT ON ?

just please clarify this ?

SET XACT_ABORT ON will use with transaction or without transactions in the procedure?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

kbhanu15 (9/10/2014)
My question is
1. When we will use SET XACT_ABORT ON in my procedures ?
a)if we have transactions in our proc then can we use SET XACT_ABORT ON ?

You can. Setting it on will automatically roll back any open transaction and abort the batch

b)if we dont have transactions in our proc then can we use SET XACT_ABORT ON ?

You can, though there's far less point in doing so. All it will do is abort the batch when an error occurs. With no transactions, there will be nothing that it can roll back.

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