In a database I am working on, there is a table called
persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it.
If I delete one row from
persons, it takes many minutes, which would not be a problem, but it also keeps the table locked, thus blocking the database for all processes. In the past, this has caused users to report that the system is down.
Of course the situation would greatly improve if I added supporting indexes for all the FKs (currently, only 20 of the 60 tables have it). But many of these FKs are for columns like
modified_by, so all the indexes would have no other purpose, and would degrade performance of the system in the daily operations, only to gain an improvement in an exceptional case.
Before running the DELETE, I already make sure that all the referencing rows have been deleted or updated. I do it manually, since I am strongly against the use of CASCADE.
I am not considering soft-delete because otherwise I would have to change all software that reads the
persons table to skip deleted rows.
Is there a way to (possibly temporarily) change the locking mechanism of the
persons table, so that even if the DELETE takes an hour, it does not impact concurrent processes?
Disabling FKs might be a possibility. The risk is that someone else creates inconsistency while I delete the row, then I cannot re-enable the FKs.
Rows to delete: typically one at a time. Either manual operation or scheduled.
Interesting point: I did not check the execution plan immediately, but apparently, almost all operations cost 0%, except 7 cases of "Clustered index Scan (Clustered)" on the PK of referencing tables; one of them costs 57%, the other from 1% to 16%. I still don't understand why it should scan a clustered index.