mibuso.com
Home› SQL Performance
Options

SQL Locking

DeputyDoghDeputyDogh Member Posts: 7
edited 2008-05-08 in SQL Performance
Navision Version 4.0 SP3

I've a question about the locking in NAV 403 on a SQL2005 database
Always rowlock = Yes

If we look at the following code, we see that we want to change the record recWhseJnlLine. That's why we've used the findset(true,false)
The result is a locking of 9 records. There are 9 records which comply to the filters set in the CAL code.

recWhseJnlLine.RESET;
recWhseJnlLine.SETCURRENTKEY("Location Code","Item No.","To Zone Code");
recWhseJnlLine.SETRANGE("Location Code",'WHSE');
recWhseJnlLine.SETRANGE("To Zone Code",'STOCK6');
recWhseJnlLine.SETRANGE("Item No.",'CAD25B');
IF recWhseJnlLine.FINDSET(TRUE,FALSE) THEN BEGIN
dQtyOnWhseJnlLine := 0;
REPEAT
recZone.GET(recWhseJnlLine."Location Code",recWhseJnlLine."To Zone Code");
dQtyOnWhseJnlLine+=recWhseJnlLine."Qty. (Base)";

recWhseJnlLine.description := 'NEW';
recWhseJnlLine.modify;

UNTIL recWhseJnlLine.NEXT = 0;
END;

When we look at the following code we want tot do some reading in the same table. We even use another variable of the same table but Navision sends alway a ROWLOCK and an UPDLOCK to the SQL once there has previously been a locking on the same table.
WHen i check : I can see that after these lines of code, 512 records are locked. The ones in the previous CAL code and the ones which have been read in the CAL code beneath.

Is this a normal behaviour or is this a bug ?


recWhseJnlLine.RESET;
recWhseJnlLine.SETCURRENTKEY("Location Code","Item No.","To Zone Code");
recWhseJnlLine.SETRANGE("Location Code",'WHSE');
recWhseJnlLine.SETRANGE("To Zone Code",'STOCK6');
recWhseJnlLine.SETFILTER("Item No.",'C*');
IF recWhseJnlLine.FINDSET(FALSE,FALSE) THEN BEGIN
dQtyOnWhseJnlLine := 0;
REPEAT
recZone.GET(recWhseJnlLine."Location Code",recWhseJnlLine."To Zone Code");
dQtyOnWhseJnlLine+=recWhseJnlLine."Qty. (Base)";

UNTIL recWhseJnlLine.NEXT = 0;
END;


It is not always common to put in commit's just to avoid this.

Can Someone help please

Thanks

DD

Comments

  • Options
    kinekine Member Posts: 12,562
    If you call locking on the table from one variable, it will lock from other variables too... it is normal behavior...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    DeputyDoghDeputyDogh Member Posts: 7
    Does this mean that when i lock 1 record to update it, and afterwards i only want to read 10.000 other records in the same table that i have to use a commit to end the transaction and then do the reading ?
    I can't imagine that this was meant to be like this.

    In my example first the 1 record is locked for the update. This seams ok. But the other 10.000 are also locked and this is causing a lot of problems.

    How can this be solved ?
  • Options
    pdjpdj Member Posts: 643
    DeputyDogh wrote:
    I can't imagine that this was meant to be like this.
    Well, I'm quite sure it is. I guess it was the way to ensure Native and SQL code to behave the same.
    DeputyDogh wrote:
    How can this be solved ?
    I guess you'll have to either add a commit or read the records before the update.
    Regards
    Peter
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Hi Bart,

    I hope the phonecall we had cleared the issue. :mrgreen:

    Yes, the 'lock level' is at transaction level and can not be reversed.
    Marije Brummel | Freelance Business Central Specialist
Sign In or Register to comment.