Sales line table locked

Mike_Heffner

Hello, We recently migrated a customer from Nav 2.5 to Nav 2009. We also implemented Lanham E-Ship and EDI. Custom code is very minimal. We are using the RTC and SQL.

We are getting the following error in some situations, "The Sales Line Table cannot be changed because it is locked by another user. Wait until the user is finished and then try again."

An example of one occurance of this is when attempting to rename an item. When it gets to the step of updating the sales lines we get the message. Waiting and repeating the rename does not resolve the issue. Rebooting the server does clear the condition. I think it is a fairly low level lock, as we can continue to process sales orders while this condition exists. And it may only apply to a few records. I have not seen evidence of a lot of records involved.

I have read through many locking related posts, but still am looking for suggestions on how to track down what is causing this. Can anyone offer any ideas on how to determine what has the lock? Can you offer suggestions on way to relieve the lock when it occurrs without rebooting the entire server every time?

Thanks,
Mike

Savatage

How often do you rename items?

When you rename of course it's going to start lock all things item related.

Mike_Heffner

Harry,

We do not do this often. This may have been the first one since the upgrade.

Thanks,
Mike

jwilder

Renaming an item will initiate a table lock (not record level) until it is done. We only do this off hours because of this. Do you have another example of where this locking is happening? Most of the time its a missing key or bad code.

Also there are way to many keys with MaintainSiftIndex = true on table 37. If you have 20000 saleslines or less it is a no brainer to disable all of them except for maybe this one:Document Type,Type,No.,Variant Code,Drop Shipment,Build Kit,Location Code,Shipment Date. This will reduce the time and overhead needed when inserting, modifying and deleting a salesline. This can make a significant difference, consider doing even if you have more than 20000 saleslines.

Mike_Heffner

Thanks Jason,

We will take a close look at this.

Our main issue now is to find out why we are getting locks. We cannot figure out how to trouble shoot this. When we try to post some invoices we get the message indicating it is locked by another user. It doesn't happen to all of them. So far we cannot determine why these particular invoices are having this problem.

Thanks,
Mike

Savatage

Some of the Reasons we get locks..
1)While importing sales orders..via data port or edi
2)multiple users posting at the same time
3)renaming of items
4)user has a dialog message they haven't answered locks others.
5) posting eship manifests
6)creating edi orders (4applies here too . While creating and a message pops up)
Fyi..Never let your slowest machines post

jwilder

You have to resolve #4. If someone has a dialog and the code is locking this must be changed. There has to be a commit before the dialog so you should restructure the code to accomodate this.

This may sound silly but post invoices off hours and import edi off hours if you can. Get a NAS going that can does this for you. During our busiest times we have had to post 9000 invoices in a day and we do it all in the evening when most users are off the system. It has made a world of difference. Are you able to do this or are you 24/7?

You could have some bad code as well. The best way to track this down is to run a process through client monitor and look for any long running code. It's pretty easy to spot.

Mike_Heffner

Harry and Jason,

Thanks for all the input. I'll keep you posted as we move through this.

Mike

David_Singleton

jwilder wrote:

Also there are way to many keys with MaintainSiftIndex = true on table 37. If you have 20000 saleslines or less it is a no brainer to disable all of them except...

Why 20,000?

jwilder

20000 is a very rough estimate. There is a point where sql can sum just as fast without an indexed view (maintain sift). I have heard some people say 5000 or 10000 but I have found 20000 or more to work for us. Most of the filters done against the Sales Line table are order specific so generally there will only be the number of lines that you have on a sales order (in our case usually 100 or less). One exception to this is the flowfield on the item table for qty. on sales order. This sums the entire sales line table so you have to make sure that flowfield still performs well after you disable the sift. If it doesn't then re-enable sift on one key that will satisfy that flowfield.

By the way if you are on C/SIDE ignore everything I just said since this is for SQL only.

Mike_Heffner

Hello,

We did finally resolve this one. It turned out to be a caused by a loop in a Lanham granule. Here is the work flow that caused the problem.

1) Create a sales order with 2 lines.
2) Ship one of the lines using E-Ship.
3) Re-open the sales order and delete the unshipped line.
4) Post the shipment invoice.

It went into a loop at that point trying to find the missing line in the sales order.

We received a fix from Lanham, and it works now.

Mike