Hi everybody.
I have a trouble with Navision. Usually when the users are posting some documents they get the error the “name table” cannot be changed because it is locked by another user. wait until the user is finished and try again but no one is modifying the same document. Somebody can tell me what could be the reason for the error?
Thanks in advance.
It doesn’t matter if someone is modifying the same document. Every document, when it posts, hits several tables that must be locked, like G/L Entry.
So say I’m posting a huge 100,000 line sales order (or it could be small, the same principle applies, it’s just easier to understand with a big order I think). In order to make sure there are no errors in the posting process, all of the data that is generated like G/L Entries, Item Ledger Entries, Value Entries, etc. is not saved until the transaction is finished. Because it is not saved, no other users know it exists yet, and more importantly, their NAV client doesn’t know it exists yet. If the table was not locked and they went to post while you were posting, their system would think it should assign an entry no to one of those ledger entries that you are already using. Then you would have two users trying to insert the same G/L Entry No. into the table. Hopefully you see why that would be a problem.
Locking is part of database activity and unless it is often and locks tables up for long amounts of time, I would say you should deal with it. If not, then search the forum for table locking and you will find many suggestions.
When this happens once in a while is OK to deal with it, but when it keeps happening all the time to every user is a nightmare, I think it’s gotta be a solution. I totally understand the Locking part of database but I also wonder if those huge companys have the same issue because it’s totally no sense to me. We have 50 users and I receive aprox 20 calls and more than 10 emails per day from users complaining about the same problem "table XX locked …wait until …blah blah. I insist It’s gotta be a solution, I am sure, but what is it ?
Alejandro, have you found a solution to this yet? Since it’s almost 4 months since your last post. I have the same problem as well, and having troubles keeping the user happy or the business process in line because of this. Ultimately, some users opted to use ‘manual’ methods to overcome this, which is really annoying since we have invested a lot for this system. What I’ve been trying to do is improve the performance of the servers, of the clients’ computers by upgrading them. Let me know if you have any solution to this problem as well.
Thanks.
Hi everybody
I agree with Leal as I have also been suffering from the problem of “Table Locking” for the past 4 years. Previously I was using “Navision 3.7” and we had faced the problem in that Version also. Now we are using “Navision 2009” but the problem still exists. We have 65 users license and every day I have to face the users complaint regarding “Table Locking”.
So, Navision gurus everywhere - please give us a real solution to this problem. Navision is ‘OK’, apart from this locking problem. Is there anyone out there, with 20+ users or so, who don’t have locking problems? What’s ur secret?
Locking isn’t a user count problem. It’s all about transactions. If you have 20 people just looking at customer and vendor cards you won’t get locking. If you have 20 people posting invoices at the same time, you will.
I have been in 100+ user systems with no SQL tuning done, and it flies 99% of the time. There is no “real solution” that can be described in a forum answer. That because there is no one answer. It could be a multitude of things from slow hardware to customizations. If it is indeed the problem for your company that they say it is, they should contact the NAV partner and request an analysis.
Unfortunatelly I haven’t found a solution, we just have to deal with it which is a nightmare, and of course it happens when 10 or more users are posting at the same time, not only people in the same department (let’s say accounting) but also receiving, shipping, etc. Users have to have to be patient and keep trying and trying until it allows them to post. This is no good at all, there is no fix for this from Microsoft because as Matt states it could be a hundred reasons causing this table locks even without customizations, it was the same when we started in 3.7 and now same thing with 4.0. What about 2009 ? any word about it? same thing?
Read this:
http://dynamicsuser.net/blogs/vanvugt/archive/2010/01/24/posting-queue.aspx
http://dynamicsuser.net/blogs/vanvugt/archive/2010/01/31/posting-queue-part-2.aspx
It might be of help to you!?
Yes, unless your company requires real time posted data, a posting queue or automated posting is the way to go. I have seen databases work very well posting the equivalent of 10,000+ orders a day this way. In short, the users don’t have permission to post, but they have permission to insert into a posting queue table. A NAS comes along and looks at that queue and posts the orders. So you only have one user competing to post, which isn’t really a competition. You may not get things posted immediately, but you can get close to real time.
Customization also plays a role in table locking.
How much customization have you done ?
Now that you mention it, let me tell you it is a lot ! Customization has been done for more than 2 years making changes and creating new functionality.
I was wondering if upgrading from 4.0 to NAV2009 will take care of it, I understand the blocking concept is related to SQL so probably the change won’t help but I’d like to hear you opinions. Regards !
It is all about customizations!
If possible you should try to have multiple users post data in standard NAV 4.0 on the same system. Just create a new database with only standard objects and use the CRONUS database and let i.e. 10 users make a sales order and post it. It should be possible and not be very time consuming.
I know that MS and originally Navision put a lot of effort into avoiding locking issues in standard code.
Also if you use the Client Monitor you should be able to find the place in code where it is time consuming and try to change this code.
What tables are blocked?
Thought I’d share my experience.
We have migrated from a proprietary database 3.7 client (objects 2.01) to an SQL database with 2009 client and have encountered locking issues before and after moving database. I got the more vocal staff to call me when the locking occurred, this with the following script enabled me to see which SQL statement repeatedly appeared when the locking was taking place. I then searched a text export of the FOBs for CAL code that referenced the Key in question i.e. SETCURRENTKEY that matched the Order By in the offending SQL.
It transpired that an update query was locking a huge amount of the table due to it using a very stupid key, even though the values of most of the primary key was available to filter by, I changed the key used to filter records to be modified to use the primary key and a majority of the locking issues just disappeared. So in this case there was no exact science just some luck, cunning and SQL posted on various web sites. Hope it helps
SELECT [User ID], [Blocked], [Blocking User ID]
FROM Session
WHERE [Database Name] = DB_NAME() AND
[Application Name] = ‘Microsoft Dynamics NAV Classic client’ AND
[Blocked] <> 0;
GO
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
GO
it might be about customisations and it might not be, the simple answer to this problem is there is no simple answer. We had a client running 4.0 on SQL with 85 licenced users of which only 5 could be on the system at any one time. Today that customer is running 140 users on 4.03 (2009 exec), with very few issues, the solution to their problems was as follows :-
Locking in NAV is not optimal, if you have a large user/transaction count then you need to make sure everything supporting the NAV system is running at its best, even if that means working through the entire environment to remove any issues, whilst hunting bad code in NAV.
We have fixed a number of clients usage and performance issues with NAV and are more than happy to look at any issues people out there are having.
We did had the same problem with one of our client.
But there the problem was of updating the analysis view every time a user posts a entry check mark in the setup.
We removed the check mark and asked them to run the updated process manually or via job queue in evening times.
Right now we are not facing any issues.