Development

SQL Server – Rollback transaction completely in case of error

Hi friends, I recently found an issue while inserting data into table during transactions that transaction will not completely rollback if we use multiple insert statement in transaction.

Let me give you a demo on this how to handle such situation.

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 primary key defined on it so ID column can contain unique values only. But here we are trying to insert duplicate value in ID column in our third INSERT statement and it should got failed.

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

1

See the third statement has thrown the error and we cannot insert duplicate key in column ID.

But what happen with the first two statement will they got inserted or rollback.

We now check the data in the table test_tran.

SELECT * FROM test_tran

2

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

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

  1. Using Try/CATCH block
  2. Using XACT_ABORT

 

  1. 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 above query we can see that no rows will get inserted into the table as it got rollback when an error occurred and we have achieved atomicity by using try/catch block.

 

  1. SET XACT_ABORT

 

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

 

So we 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 rollback the transaction when error got occurred in third statement.

So friends we can use these two method to rollback the transaction completely and achieved atomicity.

Have a happy learning 🙂

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s