SQL Locking

DeputyDogh

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

kine

If you call locking on the table from one variable, it will lock from other variables too... it is normal behavior...

DeputyDogh

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 ?

pdj

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.

Marije_Brummel

Hi Bart,

I hope the phonecall we had cleared the issue.

Yes, the 'lock level' is at transaction level and can not be reversed.