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.
Also Read..
Dirty Reads Problem in Concurrent Transactions
