What is the effect of XACT_ABORT with TRANSACTIONS

home / software / tips and tricks / What is the effect of XACT_ABORT with TRANSACTIONS

What is the effect of XACT_ABORT with TRANSACTIONS

Category: SQL Server
view more software Tips and Tricks

Updated: 03/06/2015 06:03 AM
Author: Shiju Mathews

Status: Resolved.

the state of XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Syntax: SET XACT_ABORT { ON | OFF }

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

OFF is the default setting.

The following example will help to understand the concept better.

CREATE TABLE SampleTable (ID INT NOT NULL PRIMARY KEY)
INSERT INTO SampleTable VALUES (1)
INSERT INTO SampleTable VALUES (2)
INSERT INTO SampleTable VALUES (3)
select * from SampleTable
--3 rows selected (1,2,3)
SET XACT_ABORT OFF; -- By default it is OFF
BEGIN TRANSACTION
INSERT INTO SampleTable VALUES (4)
INSERT INTO SampleTable VALUES (4) -- Statement fails but transaction is committed.
INSERT INTO SampleTable VALUES (5)
COMMIT TRANSACTION
GO
--(1 row(s) affected)
--Msg 2627, Level 14, State 1, Line 4
--Violation of PRIMARY KEY constraint 'PK__SampleTa__3214EC27624E8120'. Cannot insert duplicate --key in object 'dbo.SampleTable'. The duplicate key value is (4).
--The statement has been terminated.
(1 row(s) affected)
select * from SampleTable
--Return 5 rows(1,2,3,4,5)
/*
The previous transaction even though run time error occurred the transaction is committed and two rows inserted.
*/
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO SampleTable VALUES (6) -- Inserts 6, but the transaction is rollbacked in next step.
INSERT INTO SampleTable VALUES (6) -- Statement fails and rollbacks the entire transaction.
INSERT INTO SampleTable VALUES (7) -- Statement skipped as transaction is rollbacked at previous statement.
COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
select * from SampleTable
--Returns only 5 rows(1,2,3,4,5)
/*
In previous transaction since the state of 'XACT_ABORT' was 'ON' the transaction automatically rolled back when the run time error occurred.
*/

Indian Resources by Category

Indian Grocery Stores

Indian Jewelry Stores

Indian Hindu Temple

alt="Add your business to our listing Free"

Software Resources

Tips and Tricks

Interview Questions for C#

Interview Questions for ASP.Net

Interview Questions for SQL Server