DELETEALL without locking table

samantha73samantha73 Member Posts: 39
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

Answers

  • AudriusAudrius Member Posts: 5
    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 ?
  • AudriusAudrius Member Posts: 5
    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) ;
  • DenSterDenSter Member Posts: 8,255
    You don't need "if find", you can just issue a deleteall statement. If there are no records in the filter, nothing happens.
  • bbrownbbrown Member Posts: 3,132
    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.
    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 39
    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 :smile: - if something can go wrong it will
  • RockWithNAVRockWithNAV Member Posts: 1,073
    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.
  • bbrownbbrown Member Posts: 3,132
    Have a look at how CU 80 deletes a fully invoiced order. You will notice it does not run the table triggers.
    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 39
    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
  • bbrownbbrown Member Posts: 3,132
    Look closer at the CU80 logic. It does not run the triggers yet leaves no orphaned records.
    There are no bugs - only undocumented features.
Sign In or Register to comment.