SET XACT_ABORT in SQL Server

What is SET XACT_ABORT in SQL Server?

In this post “SET XACT_ABORT in SQL Server”, we will discuss about the XACT_ABORT command. SET XACT_ABORT controls the atomicity of any user defined transaction. By default SET XACT_ABORT is OFF. It specifies whether SQL Server automatically rollback and abort a transaction if any error occurred in any statement of the transaction. If SET XACT_ABORT is ON, if any run time error occur in any t-sql statement of the transaction, entire transaction is terminated and rolled back. If SET XACT_ABORT is OFF, only that statement which raised the error is rolled back and transaction continues the processing of other statements.

Note that if the severity of the error is high, entire transaction can be rolled back even if SET XACT_ABORT is OFF.

Scope of XACT_ABORT

Scope of XACT_ABORT is at connection level and remains until either reconfigured or connection is closed. Note that by default, SET XACT_ABORT is OFF.

Configure XACT_ABORT

To make it on use below command;

SET XACT_ABORT ON;
GO

To make it off use this;

SET XACT_ABORT OFF;
GO

When SET XACT_ABORT is OFF (Default)

Create an example to demonstrate the effect of XACT_ABORT OFF

Create a new demo table as below;

CREATE TABLE TestTable
(
ColTest INT CHECK (ColTest = 1)
)

Have a look on CHECK constraint which allows only “1” to be inserted in ColTest.

Try to insert below records in one batch as below;

BEGIN TRAN

INSERT INTO TestTable(ColTest)
VALUES(1)

INSERT INTO TestTable(ColTest)
VALUES(2)

INSERT INTO TestTable(ColTest)
VALUES(1)

COMMIT TRAN

GO

And below is the outcome of the F5;

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint “CK__TestTable__ColTe__6D2D2E85”. The conflict occurred in database “AdventureWorks2012”, table “dbo.TestTable”, column ‘ColTest’.
The statement has been terminated.
(1 row(s) affected)

Now how many number of rows in there in table TestTable? To get this use SELECT command as below;

SELECT * FROM TestTable

The subsequent statements are not terminated and rolled back but statement 2 INSERT INTO TestTable(ColTest) VALUES(2) has been terminated and rolled back.

This is the default behavior of SQL Server, because be default, SET XACT_ABORT is OFF.

When SET XACT_ABORT ON

Now just run the above insert commands with XACT_ABORT ON. Before running above inserts run below scripts too;

TRUNCATE TABLE TestTable
GO

Clear all data from table TestTable

SET XACT_ABORT ON;
GO

Change the status of XACT_ABORT settings as above. And now run the below insert commands again;

BEGIN TRAN

INSERT INTO TestTable(ColTest)
VALUES(1)

INSERT INTO TestTable(ColTest)
VALUES(2)

INSERT INTO TestTable(ColTest)
VALUES(1)
COMMIT TRAN
GO

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint “CK__TestTable__ColTe__25A691D2”. The conflict occurred in database “AdventureWorks2012”, table “dbo.TestTable”, column ‘ColTest’.

Have a look on the above error and below SELECT result.

SELECT * FROM TestTable

No row has been inserted into the table. Once error occurred during second insert, all statements have been terminated and rolled back. Now after this test you can understand the effect of SET XACT_ABORT ON and SET XACT_ABORT OFF.

If we do not use external transaction as did above with BEGIN TRAN and COMMIT TRAN, the first insert will be committed but once the second statement will raise an error, all subsequent statements will be terminated with second statement. In this case only one row will be inserted.

We can also control the commit and rollback with error handling using BEGIN TRY and BEGIN CATCH blocks in sql  server externally. I personally like to use BEGIN TRY and BEGIN CATCH to handle such type of scenarios in sql server.

Here i request you to do share your feedback and suggestion in comments below this article and don’t forget to rate this article.