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.

Create table Test_tran
(
ID Int Primary key,
Name varchar(10)
)

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

BEGIN TRAN
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')
INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')
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.

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.

SELECT * FROM test_tran

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.

Using Try/CATCH block
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.

TRUNCATE TABLE Test_tran
BEGIN TRY
BEGIN TRAN
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')
INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
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.

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')
INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')
INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')
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.

6 Comments

Join the conversation! Your thoughts help the community grow.

Ganesh Adak7y ago
Sir, This is fine but if an error occurred at the time of rollback.
Rajesh Kumar8y ago
Nice article. Thanks for sharing.............
Tomas Opis8y ago
We can see that even when the transaction failed, records got inserted ? You are still in transaction and the transaction is not commited or rollbacked. Records which you see are visible only to your session. It is visibke also to other sessions in case of setting transaction level to read uncommited. So be little bit exact on what you are trying to learn other persons. Sorry, but article in this form is misleading.
Kaushal Pareek8y ago
Thanks for the information
Ashish Christian8y ago
Nice one. Thanks for share this.
Farhan Ahmed8y ago
Nice......