How Does XACT_ABORT Work In SQL

When the value of XACT_ABORT is set to ON, the error – “The current transaction cannot be committed” occurs when another transaction is begun before a commit or rollback of the previous executing transaction. Here are few instances that invite this error:

Syntax

SET XACT_ABORT { ON | OFF }

Explanation

When ON, if there is a run-time error, the entire transaction is terminated and error is thrown.
When OFF, the Transact-SQL statement that raised the error is rolled back without any error indication.

Example:

Inserting duplicate value in a Primary Key column within a transaction.

drop table EmpSalary

drop table Emp

create table Emp([EmpId][int] primary key)

create table EmpSalary(Salary money)

set xact_abort on

begin
try

begin tran

insert into Emp([EmpId]) values(1)

insert into Emp([EmpId]) values(2)

insert into Emp([EmpId]) values(1) --duplicate value

commit

end
try

begin
catch

insert into EmpSalary(Salary) values(10000)

if @ @trancount > 0

rollback

end
catch

 

As seen in the above picture, the error occurs when the XACT_ABORT is set to ON.

You can find below that the same query executes with error and all insert statements are rolled back when the value is set to OFF.