Document Dimension Locked By Another User

Jonathan2708

Hi,

Since upgrading from Navision 4.0 to 4.0SP1 my client has been getting the following error message when adding lines to sales orders and purchase orders :

"The Document Dimension table cannot be changed because it is locked by another user"

They are using SQL Server 2000. Has anything been changed in SP1 with regards to SQL locking? Or is there some other explanation?

As I say this was never a problem before the upgrade.

Any help appreciated,

Jonathan Wareham

kriki

Probably the problem is created because Navision places page-locks on records.
You can tell Navision to place always record-locks: File=>Database=>Alter=>Tab Advanced=>"Always Rowlocks".
I am not sure but this can also slow down performance.

ha2dorff

Any solution on this one?

Joe_Little

What may be occuring here is that for a process, Navision is requesting a record level lock. SQL will escalate this to a table lock for performance purposes after a threshold is reached. At that point a table lock will be used as it yields better performance.

Kriki's solution may work, but may also cause unanticipated performance issues.

If this is what is going on, it is probably not the user that is entering the lines, they can't move that fast, and you probably don't have that many dimensions (I hope for your sake).

It is probably the result of another users simultaneous activities such as posting. This issue can be made worse by flow fields. The SQL handling of these fields can frequently cause escalation to table locks during activities such as posting.

You might want to check and see if you have SIFT fields associated with this table. If so, it could be a factor and you might want to consider turning off the SIFT maintenance.

Under SQL, you will still be able to use the flowfields, but the calculation will be done when the field is accessed rather than when it is stored/modified etc. If you find this to be the case, leave the key, turn off the SIFT maintenance and you will probably enjoy the outcome.

modric

One more suggestion - are analysis views used?

If "Update on posting" is checked for some analysis view, there may be a situation, when deadlock occurs - both posting process and view update tries to write to dimension tables...

BTW, its generally adviced to turn off these "view updates on posting", because it slows down all posting processes, which are done far more often than analysis jobs - users must simply remember to update views before doing some view-based reporting to get the most recent data.

Nobody

The are a few things that could be causing this issue.

1. Make sure the clustered index on the Document Dimension table exists. There were some issues with this in 4.01. Without this SQL will do a table scan and lock all the records

2. The current clustered index on Document Dimension has a high density and low selectivity and can cause more records to be locked than need to be.

,[Document Type],[Document No_],[Line No_],
[Dimension Code]

is not very unique you may want to try putting [Document No_] as the first field in the index to lower the density and increase selectivity which should help increase concurrency

3. Memory issues on the SQL Box. As memory utilization increase on the SQL Box it will start to escalate locks. One rowlock takes as much memory as one page lock or table lock. So if there are 100s or 1000s of individual rowlocks on a table and SQL begins to run out of memory it will escalate those locks to page or table locks to help free up memory.

4. As stated above "always rowlock' should only be used if you have A LOT of memory available on SQL Server.


Hope that helps

ha2dorff

Thanks to all of you for your replies
I believe that I maybe have found the solution to this problem. I will describe this for you tomorrow when I'm back at work.

ha2dorff

Microsoft gave me a solution, but I'm not sure that it will fix the issue 100%.

The solution I received was to do the following changes in codeunit 408:

==============================================================
Codeunit 408
Fct. SaveDocDim. and SaveProdDocDim
Move the current calls to UpdateLineDim to the following places

FUNCTION = SaveDocDim:

GetGLSetup;
IF ShortcutDimCode <> '' THEN BEGIN
IF DocDim.GET(
TableID,DocType,DocNo,
LineNo,GLSetupShortcutDimCode[FieldNumber])
THEN BEGIN
xRecRef.GETTABLE(DocDim);
DocDim.VALIDATE("Dimension Value Code",ShortcutDimCode);
DocDim.UpdateLineDim(DocDim,FALSE); //MOVED LINE
DocDim.MODIFY;
RecRef.GETTABLE(DocDim);
ChangeLogMgt.LogModification(RecRef,xRecRef);
END ELSE BEGIN
DocDim.INIT;
DocDim.VALIDATE("Table ID",TableID);
DocDim.VALIDATE("Document Type",DocType);
DocDim.VALIDATE("Document No.",DocNo);
DocDim.VALIDATE("Line No.",LineNo);
DocDim.VALIDATE("Dimension Code",GLSetupShortcutDimCode[FieldNumber]);
DocDim.VALIDATE("Dimension Value Code",ShortcutDimCode);
DocDim.UpdateLineDim(DocDim,FALSE); //MOVED LINE
DocDim.INSERT;
RecRef.GETTABLE(DocDim);
ChangeLogMgt.LogInsertion(RecRef);
END;
END ELSE
IF DocDim.GET(
TableID,DocType,DocNo,LineNo,GLSetupShortcutDimCode[FieldNumber])
THEN BEGIN
RecRef.GETTABLE(DocDim);
DocDim.UpdateLineDim(DocDim,TRUE); //MOVED LINE
DocDim.DELETE;
ChangeLogMgt.LogDeletion(RecRef);
END;



FUNCTION = SaveProdDocDim:

GetGLSetup;
IF ShortcutDimCode <> '' THEN BEGIN
IF ProdDocDim.GET(
TableID,DocStatus,DocNo,
DocLineNo,LineNo,GLSetupShortcutDimCode[FieldNumber])
THEN BEGIN
xRecRef.GETTABLE(ProdDocDim);
ProdDocDim.VALIDATE("Dimension Value Code",ShortcutDimCode);
ProdDocDim.UpdateLineDim(ProdDocDim,FALSE); //MOVED LINE
ProdDocDim.MODIFY;
RecRef.GETTABLE(ProdDocDim);
ChangeLogMgt.LogModification(RecRef,xRecRef);
END ELSE BEGIN
ProdDocDim.INIT;
ProdDocDim.VALIDATE("Table ID",TableID);
ProdDocDim.VALIDATE("Document Status",DocStatus);
ProdDocDim.VALIDATE("Document No.",DocNo);
ProdDocDim.VALIDATE("Document Line No.",DocLineNo);
ProdDocDim.VALIDATE("Line No.",LineNo);
ProdDocDim.VALIDATE("Dimension Code",GLSetupShortcutDimCode[FieldNumber]);
ProdDocDim.VALIDATE("Dimension Value Code",ShortcutDimCode);
ProdDocDim.UpdateLineDim(ProdDocDim,FALSE); //MOVED LINE
ProdDocDim.INSERT;
RecRef.GETTABLE(ProdDocDim);
ChangeLogMgt.LogInsertion(RecRef);
END;
END ELSE
IF ProdDocDim.GET(
TableID,DocStatus,DocNo,
DocLineNo,LineNo,GLSetupShortcutDimCode[FieldNumber])
THEN BEGIN
RecRef.GETTABLE(ProdDocDim);
ProdDocDim.UpdateLineDim(ProdDocDim,TRUE);//MOVED LINE
ProdDocDim.DELETE;
ChangeLogMgt.LogDeletion(RecRef);
END;
==============================================================

mootsoo

del

mrQQ

Hi,

sorry for bringing up old thread, but I just want a one confirmation: this message in first post - is this a message about deadlock occuring, and one transaction being killed, or is this a message saying that a simple lock was taking place and it's timeout has expired? If it's first case, then it's bugs in code, if it's second, then it's simple valid table block occuring, and increasing timeout value might help - correct?

DenSter

It's not a deadlock, the message would clearly say that if it were. This is a regular lock, and happens all the time. The only way to get rid of those messages is to speed up the transactions.

mrQQ

..or increase lock timeout..

thanks!

DenSter

Every time a customer decides to increase lock timeout, they change it back within a day or two, because users complain that the system gets stuck doing nothing for too long. Increasing lock timeout never solved anything.

mrQQ

Well I'm not sure about that. If one user is posting *very* long documents, and another user wants to post something during that time, sometimes its better to just wait longer, instead of retrying all the time.

DenSter

Says one person who doesn't mind waiting, or has the time to wait. Try that in a department filled with very busy people who don't have the time to wait. In my experience this never worked a bit, users always complained that they couldn't back out of their screen, or they'd simply kill the client because they thought something was fishy.

mrQQ

That all depends on client education level.

I agree that better performance = better. That's no brainer there.

Sometimes better performance = not really possible.

You spend a vast amount of resources optimizing for existing hardware and do whatever is possible (with given set of resources).

You're left with whatever performance you have.

At that point, waiting is most of the time better than retrying. It's simply more time efficient.

DenSter

mrQQ wrote:

That all depends on client education level.
<snip>
At that point, waiting is most of the time better than retrying. It's simply more time efficient.

Education level has absolutely zero to do with this, none whatsoever. This is all down to user preference, and I have never seen a situation in which users prefered to wait indefinately for a process that they don't know how long it will take them to get their computer back.

Increasing the lock timeout (or turning it off completely) is simply not the right thing to do. Let the message come up and give the user the opportunity to work on something else. Most of the time they know who is heavy on the system, and most of the time the really big processes will have been planned during 'off hours' anyway. I'm sorry but I will not concede that point. Every single time I have seen customers try this, they have changed it back within a day or two. This is from personal experience, onsite at customer sites, not thinking about this from a distant desk.

mrQQ

Well, personal experiences differ, right?

A very good example has been posted in one of the threads, where user had to keep reentering tracking lines, simply because their transaction was aborted due to lock time out, and poor implementation dropped all temporary data. This gets cumbersome quite fast.

And yes, user education level does have a very big play in all of this - if user knows, that this wait is valid, and he's standing in line sometimes it's much better to just go and make some coffee, instead of poking around and giving a shot at it at random times.

Then again, perhaps that's just my experience with live, on site customer databases and customers themselves

kriki

I always use 30 seconds as locktimeout and never got a complained. With 10 seconds I got a lot of complaints.

Just my 2 cents.

DenSter

mrQQ wrote:

And yes, user education level does have a very big play in all of this - if user knows, that this wait is valid, and he's standing in line sometimes it's much better to just go and make some coffee, instead of poking around and giving a shot at it at random times.

I just don't agree with that, simply because users always know about these locks, because they always happen, there's nothing to teach after that. It's their preference that determines whether they accept the system. If the user knows the wait is valid, but they don't want to wait 30 seconds, they will kill their session.

When the user knows that it should show the message after 10 seconds, and it doesn't, then they know that the process is running instead of waiting, and they leave it alone. They don't want to be waiting for nothing. They actuall appreciate it that the process is kicked back to them, knowing that the most they'll be waiting is however long that setting is.

All users know that the system sometimes locks up, and there is always discussion about it. At some point, there is consensus about how long the timeout should be, and that is when users accept the situation. For one company it is 10 seconds, another has it at 30 seconds, and I've seen one turn it off completely too (only to turn it back on after a couple of days). Increasing it from there is simply not the right thing to do.