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.