FWD » Core Development » Conversion Tools

The idiom of looping through a table and deleting all records in that table is fairly common for older, temp-table code, but also appears for permanent tables.

We already simplify the temp-table version of this idiom by replacing these loops with an EMPTY TEMP-TABLE statement (only in the case nothing else but the delete is occurring in the loop). We also support bulk delete with a where clause filter (for each foo where ...: delete foo. end.) for temp-tables, which is converted to RecordBuffer.delete(DataModelObject, String, Object...). Both of these are straightforward substitutions because no locking is required for temp-tables. However, the same loop on a permanent table is more complicated, because we must obtain an exclusive lock on each record before deleting it.

For a full-table bulk delete on a permanent table, a possible solution would be to integrate table-level locking into the LockManager API and implementation. Every record lock (SHARE or EXCLUSIVE) would have to first obtain a SHARE lock on the table. A bulk delete from that table would require an EXCLUSIVE table-level lock. This would be appropriate for deleting all records in a table, but not necessarily for a subset (i.e., a bulk delete with a where clause). Corollary idea: in the event the EXCLUSIVE table lock is not immediately available, we could fall back to the "old" way of deleting records in a loop.