DeleteAll

[waldo1001]

When you perform a "DeleteAll" when there is nothing to delete, it will still perform a lock. When you for example perform a DeleteAll on an empty table, it will result in a table lock.
Therefore it's good practice to always check if the table is empty when performing a DeleteAll.

[git-no]

I totally agree and in the Microsoft base app there is this pattern

Table.SetRange(field, value);
if not Table.IsEmpty() then
  Table.DeleteAll();  // can be also Table.DeleteAll(true);

[waldo1001]

Seems to comply with:
https://alguidelines.dev/docs/bestpractices/deleteall/

;-) 👍

[git-no]

@waldo1001 So when this topic is already resolved and a documentation created, why is this discussion still open? I thought I could help by contributing some information, I was not aware that this time investment is no longer necessary.

[waldo1001]

Because just because there is a guidelines, doesn't mean that people might be agreeing with it .. that's why we keep the discussions open.
We labeled the patterns/guidelines with "LIve" or "to be described" to distinguish the ones that have a live description, or not yet.. .

[jhoek]

Why should we need to worry about things like this at the application level? Wouldn't it be much more efficient if the platform checked for the absence of any records (within the filter) before locking?

[thomas-tarp]

While I agree that this is best practice, I find that the rationale behind it is slightly misleading.

DeleteAll does not perform an exclusive lock on any records if there are no records to delete. However, it does place an intent exclusive lock on the table.

This means that if the intent exclusive lock is kept long enough, it would prevent lock escalations for other transactions, thus resulting in increased memory usage on the SQL server.

Also, it means that if another transaction has already placed an incompatible lock on the table such as a shared lock or an exclusive lock (likely through lock escalation), the deleteall operation will be blocked even if there is nothing to delete.

Alexander Drogin explains it with good examples here:
https://www.keytogoodcode.com/post/if-not-isempty-then-deleteall