Set xact_abort usage

When set xact_abort is on, if a running error occurs when a Transact-SQL statement is executed, the entire transaction is terminated and rolled back.

When set xact_abort is off, only the wrong Transact-SQL statement is generated and the transaction will continue to be processed. If the error is serious, the entire transaction may be rolled back even if set xact_abort is off. Off is the default setting.

Compilation errors (such as syntax errors) are not affected by set xact_abort.

For most Ole databasesProgram(Including SQL Server), xact_abort In the implicit or display transaction data modification statement must be set to on. The only case where this option is not required is when the Provider supports nested transactions. For more information, see distributed queries and distributed transactions.

Set xact_abort is set during execution or running, rather than during analysis.

 

Usage

 

1. Overall commit or overall rollback as a transaction, in the format: Set Xact_abort On
Begin Tran
-- Todo
Commit Tran
Go

2. Each statement acts as a transaction. The transaction stops at the wrong row, and the error row is rolled back. The transaction is not rolled back before the error row. The format is:

Set Xact_abort On
Begin
-- Todo
End
Go

 

Example

BelowCodeIn this example, a foreign key conflict error occurs in transactions that contain other Transact-SQL statements. Errors are generated in the first statement set, but other statements are successfully executed and the transaction is successfully committed. In the second statement set, SetSet xact_abortSetOn. This causes a statement error to terminate the batch processing and roll back the transaction.

Use Adventureworks;
Go
If Object_id (N ' T2 ' , N ' U ' ) Is Not Null
Drop Table T2;
Go
If Object_id (N ' T1 ' , N ' U ' ) Is Not Null
Drop Table T1;
Go
Create Table T1
( Int Not Null Primary Key );
Create Table T2
( Int Not Null References T1 ());
Go
Insert Into T1 Values ( 1 );
Insert Into T1 Values ( 3 );
Insert Into T1 Values ( 4 );
Insert Into T1 Values ( 6 );
Go
Set Xact_abort Off ;
Go
Begin Transaction ;
Insert Into T2 Values ( 1 );
Insert Into T2 Values ( 2 ); -- Foreign key error. Only roll back the wrong row and continue executing the statement
Insert Into T2 Values ( 3 );
Commit Transaction ;
Go
Set Xact_abort On ;
Go
Begin Transaction ;
Insert Into T2 Values ( 4 );
Insert Into T2 Values ( 5 ); -- Foreign key error. An error occurred. Roll back all
Insert Into T2 Values ( 6 );
Commit Transaction ;
Go
-- Select shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back,
-- Xact_abort was off and rest of transaction
-- Succeeded.
-- Key 5 insert error with xact_abort on caused
-- All of the second transaction to roll back.
Select *
From T2;

 

Set Xact_abort On ;
Go
Insert Into T2 Values ( 4 );
Insert Into T2 Values ( 5 ); -- Foreign key error. The transaction is terminated in the error row. The error row is rolled back. The transaction is not rolled back before the error row.
Insert Into T2 Values ( 6 );
Go