Dynamics NAV data integrity violated by multiple service tiers

davmac1

Issue Definition: Client has multiple service tiers. If a user on one service tier makes a change to a customer or sales order, a user on another service tier can then make a change to the original data and not receive a message that another user has changed the data.
Is there a setting change we need to make to the service tiers to prevent this from happening?
Microsoft solution

Scope Agreement: The scope of this case will be to advise on the scenario in the issue definition.

I had development review this a while back in a similar case and what you’re experiencing is expected behavior. In NAV 2016, each NAV Server has a “Listener Thread” which checks to see if there have been changes approximately every 30 secs. This check is done per table. Depending on the result of the check determines whether the cache will be flushed or not. If a record is changed on the NAV Server, then the change is placed on a “list of changes”. This list is flushed approximately every 30 secs. as well. There is one change that the previous statements don’t address which is SQL direct updates. So there is also a “Timer-Flush” that was done to ensure that cache is flushed periodically. There is a setting (DataCacheSize) in the CustomSettings.config file. Changes to this value can cause performance issues if the value is set too low which would cause data to be flushed and/or retrieved more frequently.

Example
• If a change is made on NST1 and read on NST2, it could actually take as long as a minute in the worst case. However, it is more likely to be around the 30 sec. period.
• If a change is made on NST3 and read on NST3, then the record will be available immediately.

Ways to ensure the cache is flushed in a more timely manner
• Use commits to force the cache to be flushed
• Use a LOCKTABLE followed by a FIND will flush the cache for that particular table.

Problem: using the solution supplied will require changing massive amounts of standard code.
This seems like a major design flaw.
I am hoping the SQL Server experts in the Dynamics NAV group could come up with a much better solution.

SilverX

Really? Updating a record must check the timestamp. If the timestamp is different, then the mentioned error must occur. If not, this can and will cause inconsistencies and wrong dat. Reading "Old" data may occur, due to the mentioned caching, but updating old records must result in an error.

The above explanation is only correct, if the old record is not modified, then it may take up to 60 seconds for the updated data to be available on all NSTs.

Edit: If data is changed outside of NAV, this issue is expected, but not regarding changes inside the application. Please pass the above explanation back to the author and ask for instant clarification!

davmac1

Unfortunately the Microsoft support response is that this is a known problem in NAV 2016 with multiple service tiers - from the developers,
I have been invited to take it up with them at Directions next week where the support engineer will let me to talk to the project managers.
The code is obviously not checking the timestamp which I think undoes the historic NAV update design methodology.

Dave

SilverX

I still see this check in build 9.00.46773. May I ask you for the repro steps provided to Microsoft?

davmac1

if data like a customer or sales order is modified by a user using service tier 1, then another user can modify the same data when they are attached to service tier 2 and overwrite the first user's change.
Microsoft development confirmed this and gave the 30 to 60 second lag time for the changes to be seen.

mdPartnerNL

so if you use a commit, the problem is gone? or do you need to change more code?

davmac1

the problem with relying on commits is you have to change all the standard NAV pages that update tables. if changes via page updates are not safe from being overridden then the timestamp field is worthless.

SilverX

After reading your post over and over again, I cannot see any comment regarding your phrase "a user on another service tier can then make a change to the original data and not receive a message that another user has changed the data". The whole explanation is about caching and reading and also mentions direct SQL updates to be a "problem" from the application side of view.

But the author never mentions any integrity violation. My own tests confirm, that I still get this message if changes on different NSTs occur the same time. Therefore I had asked for a more detailed repro. timestamp is NOT ignored.

davmac1

I am relying on my user testing and reporting results. If they change data on one NST and then change the same data on another NST, the first user's changes are ignored. This behavior is confirmed by Microsoft. Are you testing this in NAV 2016?

jglathe

Wow, what a show stopper. Now, why would they want you to talk to them, David? They should be busy fixing this.

SilverX

Yes, I've checked this with build 9.00.46773. I get the error telling me that another user has modified the record.

If integrity violations could be the case in Dynamics NAV 2016, don't you think we would have seen a lot more complaints? Wouldn't Microsofts answer look a bit more fearfull?

Almost every concurrent write-transaction on the same data would cause immediate problems. So well, I'm speaking from my own experience: my suggestion is to question, clarify, and confirm the scenario again and probably a 3rd time. And if the problem you are facing can be reproduced in your environment, try it again on another system and confirm it again.

And as a note: A repro is much more than a sentence. It is a step by step guide to illustrate behavior to others and a possibility to self reflect on what you have done.

JasminkaT

I am not familiar with the detailed scenario nor communication with MS related to the scenario described above, but i can confirm we have made no changes in that architecture. Record changes are still governed the same way, cache synch only extends the period where dirty reads could occur, but if two users are modifying the same record, timestamp is still checked and one user should get the message 'Another user has modified...'
Not knowing the details of the scenario one can only speculate, but if users have impression their changes are overwritten - one scenario that comes to mind is if there is an intermediate refresh of the record (on the page), then the new values will be read and reapplied when the record is saved.

kriki

[Topic moved from 'General Chat' forum to 'NAV Three Tier' forum]

Lars_Westman

The problem with direct SQL updates also seems to be on a user level. I have experienced situations where I have refresed a page in NAV and can see the changes but another user (after reopening the same page) does not se the changed data until that user also press F5.