Major Deleting documents by issuing a SQL DELETE on their hierarchy row may cause two kinds of problems in some situations:
- some databases (Oracle with RAC for instance) have problems processing a large number of DELETE with ON DELETE CASCADE that apply recursively on the hierarchy table, in particular because this causes a large number of changes to the undo and redo logs,
- in a multithreaded scenario where a first thread does a DELETE on a document and therefore its hierarchy row, and a second threads tries to update the same document and that update causes a new row for a secondary table to be INSERT-ed, the row's FOREIGN KEY pointing to the deleted (in the first thread) hierarchy row will cause a constraint error.
To solve this, we introduce soft deletes. In this mode, deleting a document does not immediately cause a DELETE but just marks the row as deleted (hierarchy.isdeleted = true). Later on, cleaning of the soft-deleted rows can be performed. To avoid seeing soft-deleted rows, all queries to the hierarchy table add an additional clause checking that the row is not soft-deleted (hierarchy.isdeleted IS NULL).
