Post
Ask Question

SQL Server - Rollback Transaction Completely In Case Of Error

I recently found an issue while inserting data into a table during transactions that transaction will not completely roll back if we use multiple insert statements in a transaction.

Let me give you a demo of how to handle such situations.

  1. Create table Test_tran  
  2. (  
  3.                 ID Int Primary key,  
  4.                 Name varchar(10)  
  5. )  

Now, let’s try to insert some data into this table in a transaction.

  1. BEGIN TRAN  
  2. INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')  
  3. INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')  
  4. INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')  
  5. COMMIT TRAN  

As we can see that ID column has a primary key defined on it, so the ID column can contain unique values only. But here, we are trying to insert a duplicate value in ID column in our third INSERT statement and it should fail.

Let’s execute the query and see what will happen.

SQL Server

The third statement has thrown the error and we cannot insert duplicate key in the column ID. But what happens with the first two statements? Will they get inserted or roll back?

Let us now check the data in the table test_tran.

  1. SELECT * FROM test_tran  

SQL Server

We can see that even when the transaction failed, records got inserted; which is not the correct way. To avoid such a situation, we need to make the transaction atomic which means that either all the statements in the transaction execute successfully or none of them if any of the statements failed.

Here now, I am defining two methods to achieve the atomicity of transaction.

  1. Using Try/CATCH block
  2. Using XACT_ABORT
TRY/CATCH block

We will rewrite the query using Try/Catch block. First, I will delete all the records from the table.

  1. TRUNCATE TABLE Test_tran  
  2. BEGIN TRY  
  3.        BEGIN TRAN  
  4.        INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')  
  5.        INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')  
  6.        INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')  
  7.        COMMIT TRAN  
  8. END TRY  
  9. BEGIN CATCH  
  10.        ROLLBACK TRAN  
  11. END CATCH 

After executing the above query, we can see that no rows will get inserted into the table as it got rolled back when an error occurred and we have achieved the atomicity by using try/catch block.

SET XACT_ABORT

We can also achieve the atomicity by setting XACT_ABORT to ON. By setting XACT_ABORT to ON and we can rollback all the statements inside a transaction when an error occurred.

Thus, let's rewrite the code again in this manner.

  1. SET XACT_ABORT ON  
  2. BEGIN TRAN  
  3.        INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')  
  4.        INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')  
  5.        INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')  
  6. COMMIT TRAN 

It will also roll back the transaction when the error occurred in the third statement. So, friends, we can use these two methods to roll back the transaction completely and achieve atomicity.

Happy learning.


Similar Articles