Consequences of Timeout/Error on a Delete statement

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)

4 thoughts on “Consequences of Timeout/Error on a Delete statement

  1. ChrisM@Work says:

    That’s not a delete, it’s 10,000 deletes, each one of them within an implicit transaction. Of course they’re not rolled back, they’re done and finished. For your argument to hold any water, you must interrupt a *single* DELETE operation and show that rows are not rolled back.
    Very very misleading article.

    Liked by 1 person

  2. lokesh says:

    Yes, Chris I agree with you. It was my mistake that I considered this as a batch DELETE statement. This is not a Batch statement it is an individual delete. I will update this article

    Like

  3. Pulkit says:

    I think Chris’s comment hold true for Case 1 as well. You are deleting one row at a time due to which you are seeing one row remaining because we hit error only during that one row delete. I think If we do delete in batch then it will rollback all rows.

    Like

    • lokesh says:

      Yes Pulkit, And I have already mentioned the same at very last.
      The only reason I have not removed the incorrect part is because that others do not commit the same mistake which I did.

      Like

Shoot Your Comments!

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s