Hello Guys,

Lately, one of my friend asked me a Question, “If Delete is in progress and query get erred out or Timeout, will already deleted data will be rolled back, considering we do not have any transaction? “

My previous Answer was NO, After I executed the below statements.

I will create two tables,

PkTable – Having only one primary column.

FkTable – Have one column acting as Foreign key.

CreatingTable_With_Pkey_FKey

CASE 1 – If Query Get erred out

So the idea is, If I try to delete an id from PkTable which is also present in FkTable. Then SQL Statement will erred out. I will check if SQL Deletes all the rows other than the row having Foreign key relationship.

Let us first insert some data in my table.

Insert Records in Pkey Table

Now only one id in my FKTable, to have at-least one Foreign key relationship.

Insert Records in Fkey Table

We are ready to execute delete statement.

Delete Records in Pkey Table

From the above snapshot it is clear that our delete statement was completed with error.(Which was FKey constraint error). So let us check what we have remaining in our PKTable.

Select Records in Pkey Table After Delete

From above image, it is clear that DELETE will remove all the other records except the one for which SQL throws an exception.

CASE 2 – If Query Get Time out

What will happen if query does not get error out but got Time out due to huge number of records or some other reason.

Before i continue, I have to make some configurations. So that i can deliberately make my query to get timeout.

Choose QueryOptions as shown below.

QueryOptions

Configure the highlighted option. I have changed it to 5 seconds.

QueryOptions_ChangeTimeout

Let’s make a quick check if our configuration is working correct or not.

Check For Query Timeout

Great!.

I have repopulated my table. Now, let us do a delete.

Delete Records in Pkey Table With Timeout

Select Records in Pkey Table After Delete with timeout

As expected, all the records before timeout has been deleted.

But here is the Catch. The above Statements may seems to be correct but does not hold true. If you notice I am not deleting a batch of rows. It is just one row at a single time. It was a blunder at my side that I missed to explain (Thanks to Vikas and Chris for pointing this out).

So what I did wrong?

While statement actually making the each DELETE statement as an individual statement. So it is actually a 10000 Delete rather than one single DELETE.

Now, if we run the Delete as a batch it actually Rollback all the deleted rows if an error is encountered. Please see below snapshot.

Total number of rows before delete.

New_Select

Deleting in a batch.

New1

Let us check if we have actually deleted something?

New_Select

No change. This implies Deletion does not happened if it encounters any error.

(For other T-SQL, XACT_ABORT should also be considered. Check below link for details  https://msdn.microsoft.com/en-us/library/ms188792.aspx)

Advertisements