DELETEALL without locking table

samantha73

Hi Guys
I'm still trying to find better ways to do stuff in BC AL. I did a mistake in a loop and loop didn't stop so I had to delete records using something like below:
SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Invoice);
IF SalesHeader.FINDSET THEN
SalesHeader.DELETEALL(TRUE);

Now the I got an error saying table is locked and all got stuck. Now I have 2 questions:
1. Is there a statement to delete efficiently without locking table or some kind of NOLOCK functions?
2. On the loop, how do you set an upper limit for the loop if my code is bad and I know it will never go to 100000 records

Audrius

Hi , NOLOCK is not posible, because When you DELETE SalesHeader table records,
OnDelete triger Locking Sales lines table SalesLine.LOCKTABLE; plus its lot of records it will Lock Sales Header anyway . You need delete 100 000 records ?

Audrius

This will delete (100) SalesHeaders. (Its be faster and it don't lock table tooo long, but you need use it more times). That means other User can Use Nav and dotn call you that it's locked all time.

SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Invoice);
LoopQty:=0; //Integer variable
IF SalesHeader.FIND('-') THEN
REPEAT
LoopQty+=1;
SalesHeader.DELETE(TRUE);
UNTIL (SalesHeader.NEXT= 0) OR (LoopQty=100) ;

DenSter

You don't need "if find", you can just issue a deleteall statement. If there are no records in the filter, nothing happens.

bbrown

Locking is a "fact of life" with any multi-user database. This not unique to NAV\BC. In order to execute any CRUD statements you first need to establish exclusive access to the resource (table). That is what locking does. Since you can't avoid the lock, the goal becomes making you code efficient to minimize the locking duration and those its impact (blocking) on other users and processes.

If a very large number of orders needed to be deleted, consider build a process that deletes each order as a separate transaction. It should be able to skip any that result in errors (perhaps logging the error) and continue with the next. It should also be able to be restarted where it left off in the event of a failure.

NOTE: Since the table has an OnDelete trigger being called, the two code examples are basically the same.

samantha73

Awesome answers and thanks guys..I'm trying hard to learn better coding ..I think I can use some kind of batch deletion process using LoopQty or something similar and then pass it to a new codeunit with CODEUNIT.run method to delete per batch or record. So there are two objectives to achieve:
1. Not Locking tables for too long irrespective of good code
2. Not blowing up the system due to bad code - this I can use a limit to the loop as per Audrius but in code that create the orders in the first place so we don't end up with 100,000 records

I think it's Murphys law - if something can go wrong it will

RockWithNAV

That's the standard locking behavior of NAV/BC System. Don't use FINDSET, it's not needed whenever you use, DELETEALL,MODIFYALL.

Normally if I have a custom tables I use SQL Query to delete objects.

bbrown

Have a look at how CU 80 deletes a fully invoiced order. You will notice it does not run the table triggers.

samantha73

bbrown wrote: »

Have a look at how CU 80 deletes a fully invoiced order. You will notice it does not run the table triggers.

If I don't run the table triggers then I get orphaned sales lines when deleting sales header ..looks like when deleting un-posted sales invoice you need to run table triggers

bbrown

Look closer at the CU80 logic. It does not run the triggers yet leaves no orphaned records.