Best practices deleting many records from several tables in SQL Server
<p>Having a parent table with millions of records and three child tables with foreign keys pointing to the primary key of the parent table. Like so:</p>

<pre><code>  Parent
 parent_id (PK) \       Child1
                |     child1_id (PK)
                |---- parent_id (FK)
                |
                |      Child2
                |     child2_id (PK)
                |---- parent_id (FK)
                |
                |      Child3
                |     child3_id (PK)
                |---- parent_id (FK)
</code></pre>

<p>What would be the best practice to hard delete hundreds of thousands of records from <code>Parent</code>? I want to delete on the following condition: <code>DELETE FROM PARENT WHERE [STATUS] = 'DONE'</code>. Is there a way not to lock the table(s) while the delete is happening? So that other records can be inserted in all those tables?
Options I can think of:</p>

<ol>
<li>use <code>CASCADE DELETE</code> on the foreign keys.</li>
<li>use a soft delete: Start a transaction, <code>UPDATE parent SET [DELETED] = 1 WHERE [STATUS] = 'DONE'</code>, delete each child with those parent ids, then hard delete the parents and commit.</li>
<li>similar to 2. but using a procedure and saving those ids to be deleted in a table variable so that I don't need to add a new <code>[DELETED]</code> column to the <code>Parent</code> table.</li>
<li>Select Ids to be deleted <code>SELECT parent_id FROM parent WHERE [STATUS] = 'DONE'</code> and then do a batch delete passing all these ids. (This performs really badly, so I'm discarding it).</li>
</ol>

<p>I'm using SQL Server 2014 and spring jdbc.</p>