Warehouse activity Lock

EtienneRoy

Hi

I use Navision 5.0 And I have some DeadLock probleme whit table Warehouse activity Line

On the Unit Code 7312 Create Pick You have a function "CreateWhseDocument"
Begin with locktable Like This

WhseActivHeader.LOCKTABLE;
IF WhseActivHeader.FIND('+') THEN;
WhseActivLine.LOCKTABLE;
IF WhseActivLine.FIND('+') THEN;

I make some Lock test and I find that this code lock all table for any action Modification
It's make some probleme when some people just want to modify a pick.

I want to replace this code with new fonction to make sure just the new record is lock not all like this.

IF WhseActivHeader.FINDLAST THEN;
WhseActivHeader.LOCKTABLE;
IF WhseActivLine.FINDLAST THEN;
WhseActivLine.LOCKTABLE;

When I make some test all work very good any peaple can chage some value on other pick but just the new pick is lock or
if you want to create a new pick is lock to.

So i thinks is the best for more performance but i need to know if you think is good

Regards

DenSter

You can replace FIND('+') with FINDLAST, but don't move the LOCKTABLE statement. That is there to serialize the transaction. It's not going to make a big impact though.

EtienneRoy

Hi

Thanks for your request,
but my big question is why Navision whant to Lock all Whs Line table for any Type (Put-away,Pick,Mouv)
just for add new document, the table Warehouse Activity Header have the primary key Type and No

On the Insert trigger you have the Locktable for the Serie No Line this is really the information you dont want to have in double. If is locked by a other user is normal you cannot create a new pick document.

but you dont lock all people work on any Put-way, pick or mouvment ?

Whit the Warehouse Activity Line Locktable this is bigger problème. if i have a process to create a batch of pick document
all modification of any line cannot make until the process is done ?

DenSter

Don't let the command fool you into making assumptions about what it really does. The command is LOCKTABLE, because on the C/SIDE database server it did lock the whole table.

On SQL Server it does not lock the entire table, just the last one. The LOCKTABLE command causes the isolation level to be high enough to actually lock the record instead of just retrieving it. Due to the primary key of the table, that in effect locks the table for everyone else, because every transaction wants to insert the same next entry.

FIND('+') vs FINDLAST has no effect on what is locked, it is simply how much data is retrieved. With FINDLAST it is the last record only, and with FIND('+') it can be many more records. The performance gain is in the number of records that are transported over the network, not what is locked.

EtienneRoy

Ok i understand, I just make sure the process on this table is clean because we have a lot of deadlock on the warehouse activity line

but in the case I want to modify just one pick document
and make sure all other pick document not lock with that i can make this

RESET;
SETRANGE("Type"...
SETRANGE("No.",...
IF FINDSET(TRUE) THEN BEGIN
LOCKTABLE;
REPEAT
...
UNTIL NEXT = 0;

Because some time in Navision I find this exemple not for create new line just for modify or validate

WhesLine.LOCKTABLE;

WhesLine.SETRANGE(...
IF WhesLine.FIND('-') THEN REPEAT
UNTIL WhesLine.NEXT = 0;

so this pratice lock all table even if we want juste change one document

thanks you for your advice

DenSter

You're not really understanding what I am saying.

The LOCKTABLE command DOES NOT lock the table. What you are saying about locking the whole table is wrong, it does not work that way.

The LOCKTABLE command causes the query that NAV sends to SQL to be with an isolation level that says "I am going to modify this so I need full permission on the record". In order for that to work, LOCKTABLE MUST be done BEFORE the FIND command, because otherwise it does not get the right permission. If you put LOCKTABLE AFTER the FIND command, the system will have to retrieve the same record later on with the right permission. Moving the LOCKTABLE to after the FIND command DOES NOT fix your deadlock. You only think it does because you are changing the way the system behaves.

EtienneRoy

Ok sorry i check my test and i know you you said

So if I have a big warehouse receip for sales return and at same time a big Warehouse shipment for sales document
eatch document can have 130 line

when we try to report this both document we have some probleme because all the time one user block by the orther user
and if you put a other people who want to validate a other document is really crazy

so for have better perform make sur my key is good .. index is good ...

what can make some problem of performance

I know my question is not very clear but what you thinks about that ?

stryk

Hi there,

well, I try to support Daniel's explanations a little bit - maybe you want to check out this, too:
http://dynamicsuser.net/blogs/stryk/archive/tags/Locking/default.aspx

The thing with LOCKTABLE:

NAV reads uncommitted data by default, aka "Dirty Reads". The only chance to grant "Clean Reads" in NAV is using the LOCKTABLE command (or FINDSET(TRUE) command). As Daniel explained, this is not locking the table: the TRANSACTION ISOLOATION LEVEL is set to SERIALIZEBLE, then the following reading command (FIND, FINDSET, etc.) will fire a SELECT containing an UPDLOCK hint. Actually the UPDLOCK is causing the blocking conflict, if two (or more) processes execute the NAV LOCKATABLE code ...

In your first example, you were using FIND('+'): this creates a Cursor, loading the whole table into cache (SELECT * without WHERE), thus UPDLOCKing all the records. Of course, that "feels" like a table-lock, technically it isn't.
The FINDLAST instead fires a SELECT TOP 1 * without WHERE, hence, just locking the last record in that table. From NAV perspective the desired result is the same, but without causing super-load on the server and without maximizing blocking issues.

Using a LOCKTABLE after the FIND (or whatever) command is pointless, as the real locks are engaged with the SELECT statement.

So when it is about LOCKTABLE, it's finally "data-integrity versus blocking". Changing the order of such commands or removing explicit locking commands could solve blocking issues, but corrupt data. The whole blocking "business" is somewhat tricky and really needs to be handled with care!

what can make some problem of performance

Well, there are gazillion of issues, and plenty of solutions. If you search MIBUSO about "SQL Performance" you'll get tons of advices and reccommendations!
And in addition, there are many useful BLOGs from various MS NAV teams, MVP and many more! You could find a link list on my BLOG: http://dynamicsuser.net/blogs/stryk/

herrlutz

Hi,

if you run in locking problems by warehouse postings try the following small modification in Table 5769 "Warehouse Setup", Function "GetNextReference":

//START
EXIT(100);
//STOP
LOCKTABLE;
GET;
"Last Whse. Posting Ref. No." := "Last Whse. Posting Ref. No." + 1;
MODIFY;
EXIT("Last Whse. Posting Ref. No.");

Before we did that change, the table "Warehouse Setup" was TOP2 (after no. series lines :-) in lock- causing tables.

Brett_Barabash

My cautious, pessimistic side thought this couldn't possibly be true so I did some research.

I ran a text search of all standard code in NAV2009 R2, and the only code that directly references the GetNextReference() function is codeunit 7324 Whse.-Activity-Post. The unique number generated by this function is carried over to a field named Posting From Whse. Ref. in the related Sales Header, Purchase Header, etc. records.

Now here's where it gets interesting. One would think that this number would be carried through the system for auditing, but it doesn't. The source document posting codeunits (80, 90, etc.) pull the value into a variable named WhseReference and immediately clear the field in the record. Then anywhere it needs to do a warehouse-specific task it simply checks if WhseReference <> 0.

So while registering a pick, for example, the Warehouse Setup table remains locked until the posting is complete so it can guarantee the reference number generated is unique. However the rest of the system doesn't care if the number is unique, only that it is not 0. Hard coding 100 or 1 or 1000000 as the reference seems to have the same effect without locking anything.

Has anyone else heard of this before? Why did they build a bottleneck like this into the code?

herrlutz

Hi Brett,

before we put in "Exit (100)" as mentioned above, we ran the same analysis like you did and came to the same result.
This "GetNextReference" in Navision standard is obsolete, cause no other code makes a real use of this reference no.
It´s only important to have a reference not Zero.

regards
Alex

jglathe

Hi there,

... this fits to my mood on NAV ... ](*,) Thank you for the analysis and posting it here.

with best regards

Jens

jflynn

Upgrading to 2013 r2. Does anyone know if this suggested fix should be brought forward to that version?

jglathe

jflynn wrote:

Upgrading to 2013 r2. Does anyone know if this suggested fix should be brought forward to that version?

Hi Joe,

just did a check with Stati-Cal Prism against NAV2013R2 W1 RU9. The answer is yes, this is still the case. Prism is a really useful tool, btw.

with best regards

Jens