Home » SQL SERVER SET XACT_ABORT

SQL SERVER SET XACT_ABORT

SQL Server SET XACT_ABORT controls the atomicity of any user defined transaction.





By default SET XACT_ABORT is OFF.

IF SET XACT_ABORT is ON, in case if a Transact-SQL statement raises a run-time error then the 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 and it also depends upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

SYNTAX

SET XACT_ABORT { ON | OFF }

Lets look at an example of SET XACT_ABORT in SQL Server.

SET XACT_ABORT OFF

Following statement creates a table Person that have a check constraints (Age > 18) on column Age that will allow value those are greater than 18 else raise an error.

By Default SET XACT_ABORT is OFF, so do not need to set it OFF.

CREATE TABLE Person 
(
ID INT IDENTITY(1,1) PRIMARY KEY, 
Age INT CHECK (Age >18)
)

Lets run the following T- SQL code.

 

BEGIN TRANSACTION
INSERT INTO Person VALUES (19)
INSERT INTO Person VALUES (22) 
INSERT INTO Person VALUES (35)
INSERT INTO Person VALUES (17)
INSERT INTO Person VALUES (45)
INSERT INTO Person VALUES (28)
COMMIT TRANSACTION
GO

 

As you see, The subsequent statements 1, 2, 3, 5 and 6th are inserted except the 4th statement that is terminated and rolled back of an error of CHECK constraint on Age column as the value provided is less than 18.

Lets check the table to see, how many records are get inserterd.

 SELECT * FROM Person

As you can see, all records are inserted except the 4th inserted statement that raised an error of check constraint and rolled back.

Lets see, what happens when you use SET XACT_ABORT ON

SET XACT_ABORT ON

Lets remove all records from table first, and run above scripts again with SET XACT_ABORT ON.

Following truncate statement removes all the records from table.

TRUNCATE TABLE Person




Lets execute same scripts again but this time we set XACT_ABORT to ON.

SET XACT_ABORT ON ;

BEGIN TRANSACTION
INSERT INTO Person VALUES (19)
INSERT INTO Person VALUES (22) 
INSERT INTO Person VALUES (35)
INSERT INTO Person VALUES (17)
INSERT INTO Person VALUES (45)
INSERT INTO Person VALUES (28)
COMMIT TRANSACTION
SET XACT_ABORT OFF ;
GO

You can see, again you get the same error is returned due to check constraint, Lets check the table to verify how many records are inserted into table.

 SELECT * FROM Person

You can see, this time no row is inserted into table as you have used SET XACT_ABORT ON. So, when an error occurred on 4th statement all  the statements are terminated and rolled back.

You can also control the commit and rollback with error handling using TRY…CATCH blocks in sql  server.

Dirty Reads Problem in Concurrent Transactions

SQL Server Advance




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.