FINDLAST and SQL Cache Queries

Business Central/NAV Technical Forum
1

Hello everyone.

I’m recurring to the experts because this issue is driven me nuts. =P

I have a custom Table to save packing boxes information. The “PackBoxHdr” table contains a Primary Key that is a NoSerieMgt Value (Code 20), so it is working fine but also has a secondary Key that has a Document No. (Code 20) and a Carton No. (Int), when a user creates a new box from a page it runs a Codeunit funtion to retrive the last packing box and create a new one for a Document No Value.

Code:

T_PFPack.RESET;
T_PFPack.SETCURRENTKEY("Document No.","Carton No.");
T_PFPack.SETRANGE("Document No.",TempSourceNo);
IF T_PFPack.FINDLAST THEN 
  TempCartonNo := T_PFPack."Carton No.";
  
TempCartonNo += 1;  
  
...  

WITH T_PFPack DO BEGIN
  INIT;
  "Internal Carton No." := TempCartonId;
  "Document No." := TempSourceNo;
  "Carton No." := TempCartonNo;
  INSERT;
END;

Now the issue is that if two users create a new carton for the same Document both user get the same Carton No creating two boxes with the same number. I have tracked down to believe that if both users the carton at almost the same time the second user gets a “ghost deadlock” and uses the cache information of the first one, so both users create the same number or at least this is my conclusion, also I’ve been looking for LOCKTABLE or a similar case but without any luck.

2

You must add a call to LOCKTABLE(T_PFPack) before calling FINDLAST to prevent this kind of concurrency problem.

3

I’m going to probe your sugestion, this table is a concurred one for a custom packing procedure. I’m fearing that could cause a deadlock problem, however, in my test enviroment works ok, so fingers crossed and i will comment back if it solves the issue.

4

Thanks jdeaguiarklh, it works like a charm.