G/L Entry Table Lock

cacilley

I have a client who is trying to Post a Sales Order as well as Post a Receipt and Invoice for a Purchase order and they receive this error:

"The operation could not complete because a record in the G/L Entry table was locked by another user. Please retry the Activity."

The client has recently been upgraded to 2013R2, but this issue only manifested about 2-3 weeks after the upgrade.

I understand this issue if because someone else is trying to post when someone else is, but I do not understand why it is happening so much now? Could it be a timeout issue or slow server? Any help is appreciated. Thank you.

rico1931

This also happens when people are running huge reports with no filters...

Also you can check the "Session List" and see what user ID's have more than 1... this means they were running a report and decided they didn't need it then exited out of nav... well this leaves the report running on the server and locks people out. You have to write or change the table property a little but you can kill the session from the list..

http://www.mibuso.com/forum/viewtopic.php?t=56261

bbrown

rico1931 wrote:

This also happens when people are running huge reports with no filters...

Also you can check the "Session List" and see what user ID's have more than 1... this means they were running a report and decided they didn't need it then exited out of nav... well this leaves the report running on the server and locks people out. You have to write or change the table property a little but you can kill the session from the list..

viewtopic.php?t=56261

NAV uses "Read Uncommitted" isolation when running reports. Meaning they issues no locks and ignores other existing locks. Therefore a NAV report does not lock anything and will not produce this situation. The exception being any reports that contain a process where they are populating a "reporting" table, that is then used to product the report's output.

BardurKnudsen

To answer the original question: The "The operation could not complete because a record in the G/L Entry table was locked by another user. Please retry the Activity." message is not an error message as such. It is an information to the user that a certain table has been locked for such a long time by someone else, that we are not going to wait any longer. Just like when you phone someone, and they don't pick up the phone - then after a while you receive a time-out.
The reason is two-fold: First, of course, that some processes are taking a long time. You suggest that it could be because of a combined sales/receipt/invoice process which locks the G/L Entry table. I don't know how many lines your typical order has, but if we assume that the entire process takes 5 seconds, then if 5 people attempt to do it at the same time, the 2nd user will have to wait for 5 seconds before she can get hold of the G/L Entry table, the 3rd will wait for 10 seconds etc. Compare this to the timeout setting in the File|Database|Alter Advanced tab settings in C/SIDE. Often this timeout is set quite low, meaning people are thrown off quite early. If it e.g. is set as low as 30 sec. then try to change it to 60 sec. In the demo database it is set to 10 seconds which doesn't allow for much locking.

generic

There are many clients who have upgraded to 2013 and have issue identifying the person who is posting the a transaction.

MS needs to add an option in the xml config file to make the sql session ID be the same as NAV Session ID.

Yes the service tier will use more memory but Let the client/consultant decide on setting.

Locking and performance is the single most import problem with NAV and hasn't done much to fix it.
It's sad that in the above topic MS is asking how many lines are there on the sales line.

bbrown

Actually asking how many lines are in the Sales Order is a very important diagnostic question. It goes to the point of determining whether posting is taking so long simply due to the size of the document. Or is the document small, and therefore more likely underlying performance issues. Or a combination.

Asking questions to better understand the situation is the first step in diagnostics of a problem. I, for one, have no problem with MS asking that question. In fact, I'd be more concern if they did not ask.

generic

There shouldn't be any locking if you are posting a transaction. It should not lock some other user posting some other transaction.

And if you are modifying or designed the system to write to the same resource, then you have bad design.

Having all the ledger/sub ledger as Entry No. as PK is horrible design for concurrency.

Using No. series lines is horrible design for concurrency.

Using Index View is horrible design for concurrency.

Calculating Unit cost on item card/ stock keeping unit on most transaction is horrible for concurrency.

bbrown

I don't entirely disagree with all of your statements. However, locking is a fact of life with multi-user databases. You cannot change a resource without first acquiring exclusive access (the reason for locking). But yes, the goal should be to minimize locking to only that which is required.

davmac1

Another area to look at is the number of keys in a table.
I cut down the number of SQL maintained keys in the sales line from close to 30 to under 10 and the posting time for a sales order was cut by about 67%.
I don't remember the exact number of keys involved, but they were excessive.

bbrown

davmac1 wrote:

Another area to look at is the number of keys in a table.
I cut down the number of SQL maintained keys in the sales line from close to 30 to under 10 and the posting time for a sales order was cut by about 67%.
I don't remember the exact number of keys involved, but they were excessive.

I have taken similar approaches on many sites, but also find you need to take how the system is used into consideration. This can work very well in situations where there may be a lot of sales activity but typically very few sales orders at any give point. A distribution or direct to customer scenario may be an example.

However in situations that involve large numbers of orders existing, this may have a negative impact. Particularly with things such as the production planning tools. Removal of sales line indexes and VSIFT can improve posting performance, but the trade-off can be reduced performance of the planning tools.