xact_abort on vs begin transaction

Question

xact_abort on vs begin transaction

Heisenberg 261

hi folks,
Whats the difference between setting xact_abort on vs begin transaction in a batch? do they behave same ?

4 answers

Your answer

Answer 1

It's like the difference between an apple and bicycle. That is, they are not the same at all.

BEGIN TRANSACTION starts a transaction, and this transaction will remain option until it commits or rolls back. In the latter cases, all updates performed after BEGIN TRANSACTION will be rolled back. Also, until, you commit the transaction, the changes performed in the transaction is only visible to the process running the transaction.

SET XACT_ABORT ON does not start a transactions, so subsequent updates are visible to all processes and cannot be rolled back.

SET XACT_ABORT ON controls what happens on an error. With the default setting, XACT_ABORT OFF, any of these things can happen if there is an error during execution:

The connection is closed. (Internal error in SQL Server.)
Execution is aborted, and any open transaction is not rolled back.
Execution is aborted, but any transaction is left open.
The current scope is aborted, and execution continues on the next statement in the invoking scope. There is no roll back.
Execution continues on the next statement. The statement is rolled back, but any open transaction is not.

With XACT_ABORT ON, only 1 and 2 can happen. Well, almost. There are a few errors, where XACT_ABORT is ignored, and nothing is terminated.

Tom Phillips 17,771 Reputation points

2022-07-23T02:22:07.157+00:00

I like to say "you are comparing an apple to an automobile". Besides starting with A, they are not comparable.

Answer 2

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Heisenberg ,

No, they are not same. Please reading below similar threads, you get the difference between them, hope they could help you.

What is the point of TRY CATCH block when XACT_ABORT is turned ON?
How to use SET XACT_ABORT ON the right way

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Heisenberg 261

Thank you all.
@Erland Sommarskog currently im using block like this, In this while loop im doing insert and then delete in batch of 5000 rows. Currently if for some reason DELETE statement fails INSERT succeeds for any loop iteration, i want insert to rollback if delete fails for that iteration. Can I achieve this using set xact_abort on in the beginning?

begin try  
  
while loop  
begin  
insert into table...  
  
delete from table...  
end  
end try  
begin catch  
if @@trancount > 0  
rollback  
end catch

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-07-26T19:03:34.227+00:00
Currently if for some reason DELETE statement fails INSERT succeeds for any loop iteration, i want insert to rollback if delete fails for that iteration. Can I achieve this using set xact_abort on in the beginning?

No. What you achieve, though is that the script will stop executed, but what has been inserted has been inserted.

To achieve what you are asking for, you need to wrap the INSERT and the DELETE in BEGIN-COMMIT TRANSACTION. That is,

BEGIN TRANSACTION insert into table... delete from table... COMMIT TRANSACTION.
Heisenberg 261 Reputation points

2022-07-27T23:42:12.8+00:00

Thanks @Erland Sommarskog so my current script (without using begin tran and xact_abort on) is achieving the same result.. if delete fails then it goes to the catch block and it stops executing if DELETE fails , so whats the point of using XACT_ABORT ON ?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-07-28T07:57:41.97+00:00

I'm starting to lose you. First you say "Can i use XACT_ABORT?", then you ask "what's the point?".

What is your real concern?
Tom Phillips 17,771 Reputation points

2022-07-28T17:22:57.14+00:00

If you do not put your code in an transaction, there is always a chance the INSERT will succeed and the delete will fail, leaving the data in an invalid state.

xact_abort has nothing to do with that functionality. Some errors are not terminal unless you use xact_abort. But you do not have an explicit transaction. So that does not apply to your situation in any way.

See: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-ver16
Heisenberg 261 Reputation points

2022-07-28T22:21:12.13+00:00

@Erland Sommarskog apologize im not able to clarify. As you said, when setting XACT_ABORT on will abort the connection and execution is aborted too. I see same behavior when executing my begin try and end try /begin catch -end catch block with XACT_ABORT setting (so i assume by default its off). I ran my script with a very small tran. log space so that it will error out. It errored out and script was stopped. So how different is it with using XACT_ABORT on.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-07-28T22:25:41.357+00:00

I still don't understand what you are asking about. If you want the INSERT to be rolled back, if the DELETE fails, you need to wrap in a transaction.

XACT_ABORT is best practice, because it reduces the number of things that can happen when there is an error, and it also makes sure that the transaction is rolled back. But it is not a replacement for BEGIN TRANSACTION.

Answer 4

Tom Phillips 17,771

You should be using a transaction inside your loop

begin try  
      
 while loop  
 begin  
BEGIN TRAN  
 insert into table...  
      
 delete from table...  
COMMIT  
 end  
 end try  
 begin catch  
 if @@trancount > 0  
 rollback  
 end catch

Share via

xact_abort on vs begin transaction

4 answers

Your answer