SET XACT_ABORT, to rolls back current transaction on Run-Time Error

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

To understand more on it we will do small practical, here we are creating two tables – table1 and table2 with primary key in first table as Parent Table and Foreign Key in second table as Child table.

create table table1

    (col1 int not null primary key);

create table table2

    (col1 int not null references table1(col1));

Go

Command(s) completed successfully.

Our table is ready, now lets insert some records in table 1, which has primary key on col1.

insert into table1 values (1);

insert into table1 values (3);

insert into table1 values (4);

insert into table1 values (6);

go

Now we are setting xact_abort to  off and inserting three records in child table table2 which is referenced by table1 primary key column.

set xact_abort off;

go

begin transaction;

insert into table2 values (1);

insert into table2 values (2); -- foreign key violation.

insert into table2 values (3);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

The statement has been terminated.

(1 row(s) affected)

select * from table2

col1

-----------

1

3

(2 row(s) affected)

In the above insertion the seocnd records which we were inserting was violating the Integrity constrain, since we were trying to insert value 2 which is not present in col1 of table1.

Eventhough the second insertion fails, it didn’t stop execution there and proceeds with the third row insertion and finally commited the 1 and 2 rows insertion implicitly. This is what happends due to xact_abort to  off.

   

Conclusion: SELECT shows only keys 1 and 3 added.  Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded.

Now lets set xact_abort to  ON and tries to insert some good and bad records.

set xact_abort on;

go

Command(s) completed successfully.

begin transaction;

insert into table2 values (4);

insert into table2 values (5); -- foreign key violation.

insert into table2 values (6);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

select * from table2

col1

-----------

1

3

(2 row(s) affected)

Here from the output we can see none of the records gets inserted to the table even though the first and third records comply with the Integrity constrain. This is because, on the failure of the insertion of 2 records which was violating the integrity, the complete transaction rollback.

Conclusion: Key 5 insert error with XACT_ABORT ON caused  all of the second transaction to roll back.

Note: The alternative to this is TRY CATCH block

Take up a piece of code, blend it with ur logic, think dream n live it, let ur every part full of that logic, leave everything aside.. This is how Programmer lives their life. WILL U MARRY ME J  

Post Reference: Vikram Aristocratic Elfin Share