Post
Ask Question

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







  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.

 




                        
                    

					 
					 

					 
					 

                    

                        
                        

                            
Similar Articles