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

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 11,627 total views,  1 views today