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







  1. SET XACT_ABORT { ON | OFF }  
    2. 





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.








  1. drop table EmpSalary  
  2.   
  3. drop table Emp  
  4.   
  5. create table Emp([EmpId][int] primary key)  
  6.   
  7. create table EmpSalary(Salary money)  
  8.   
  9. set xact_abort on  
  10.   
  11. begin  
  12. try  
  13.   
  14. begin tran  
  15.   
  16. insert into Emp([EmpId]) values(1)  
  17.   
  18. insert into Emp([EmpId]) values(2)  
  19.   
  20. insert into Emp([EmpId]) values(1) --duplicate value  
  21.   
  22. commit  
  23.   
  24. end  
  25. try  
  26.   
  27. begin  
  28. catch  
  29.   
  30. insert into EmpSalary(Salary) values(10000)  
  31.   
  32. if @ @trancount > 0  
  33.   
  34. rollback  
  35.   
  36. end  
  37. catch  
    38. 







 




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.