Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application.

Basically, the delete is executed from a stored procedure, which first does a couple of selects to define the data to be deleted, after which it starts deleting from different tables. As the link between this data is essential for the deletion, it has to be avoided to delete for example and order without deleting some dependencies. Therefore it has to run in one transaction.

Problem is, whenever the script is running, the application itself become not unusable: timeouts when getting data from the web, or trying to update a certain record. Al those queries are blocked by the sessions that runs the transaction

The data being deleted is not relevant anymore, and should thus not be updated by the application.

I've tried running the transaction in different isolation levels, including snapshot, but it still doesn't work.

How can I avoid these locks? Should I use READ_COMMITTED_SNAPSHOT?

Thanks in advance...

share|improve this question
READ_COMMITTED_SNAPSHOT would let the app still read the data while you are deleting, but updates will still be blocked as writes still block writes in Snapshot Isolation. – Ali Razeghi Sep 4 '13 at 17:49

2 Answers 2

I don't think you have to force all of your deletes to occur within a single, monolithic transaction. Instead of having a transaction that does:

  DELETE all the things from child table 1;
  DELETE all the things from child table 2;
  DELETE all the things from child table N;
  DELETE all the things from parent table;

Why not delete in chunks? You can play around with the TOP (?) parameter based on how many rows lead to what kind of duration of transaction (there is no magic formula for this, even if we did have a lot more information about your schema). Pseudo-code:


SELECT @rc = 1;

WHILE @rc > 0
  DELETE @p;

  INSERT @p SELECT TOP (?) primary_key FROM parent table 
    WHERE (clause that defines the data to be deleted);

  SET @rc = @@ROWCOUNT;

    DELETE child table 1 WHERE parentID IN (SELECT p FROM @p);
    DELETE child table 2 WHERE parentID IN (SELECT p FROM @p);
    DELETE child table N WHERE parentID IN (SELECT p FROM @p);

    DELETE parent table WHERE parentID IN (SELECT p FROM @p);
  -- to minimize log impact you may want to CHECKPOINT
  -- or backup the log here, every loop or every N loops

This may extend the total amount of time that the operation takes (especially if you backup or checkpoint on each loop, or add an artificial delay using WAITFOR, or both), but should allow other transactions to sneak in between chunks, waiting for shorter transactions instead of the whole process.

I wrote a lot more about this technique here.

share|improve this answer
The problem is that one row of the parent table contains a lot of child elements, and I am already deleting the parent record per record. Most dependencies are deleted on cascade. I suppose I could go on level deeper, delete from there and after that delete the top record. Is there no way to run this transaction is some kind of rowlocking mode, because theoretically, the data which is being deleted is no longer accessed by the application? Thanks for your extensive answer btw. – coussej Sep 5 '13 at 6:58
You need smaller chunks to help avoid lock escalation, i.e. page or table locks which is what is causing the contention with other queries. By default lock escalation occurs after 5000 locks are taken on a table within a transaction. – Ted Elliott Jan 23 '14 at 4:52

We have a similar situation and we had to create a larger script to resolve deletion in a proper manner.

Note: Another issue you will run into with large delete operations (one or many transactions), is that your transaction logs will grow really fast during the runtime of the delete. So, be prepared for that in terms of disc space.

It may be required that you can mark a parent dataset as "deleted" for this to work properly with your application.

We have resolved the issue as follows:

  • create a working table, where all the parent records you plan to delete are selected into. This table will record the parent record to be deleted, and some time-statistics, when did you elect it for deletion, when did you start do delete and when were you finished. The parent object is only referenced by it's primary key.
  • create in the same table if feasible or add more worker tables for the children of the parents, statistics are optional, but for this to properly work, you need at least a state "successfully deleted" or "to be done".
  • Now select-insert all doomed record's primary keys into the work-tables
  • Having done this, you are now ready for mass-deletion:
    • create a cursor for the leaves of your tree and start deleting them one by one or a bunch of them per transaction.
    • When there are no more outer leaves left, continue with the next level, until you are at the parent objects.

This seems like a very hefty amount of work "just to delete", but using this approach, your deletes will create only very, very short living transactions, with very short-living locks. The locks will always cover only one table and thus pose a very low risk to disturb someone.

If your delete takes too long, you can stop the process at any time and restart it later. You can even stop the process, add more records and restart the process.

You will have no partially deleted records because you tracked the deletion state for each of them.

Note 2: What we did not do, but what is also valid, is to collect the children in the moment you want to delete the parent. Still, you follow the one-record-by-one delete strategy to keep transactions small and locks short.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.