ArcherPoint Dynamics NAV Developer Digest - vol 192
The NAV community, including the ArcherPoint technical staff, is made up of developers, project managers, and consultants who are constantly communicating, with the common goal of sharing helpful information with one another to help customers be more successful.
As they run into issues and questions, find the answers, and make new discoveries, they post them on blogs, forums, social media...so everyone can benefit. We in Marketing watch these interactions and never cease to be amazed by the creativity, dedication, and brainpower we’re so fortunate to have in this community—so we thought, wouldn’t it be great to share this great information with everyone who might not have the time to check out the multitude of resources out there? So, the ArcherPoint Microsoft Dynamics NAV Developer Digest was born. Each week, we present a collection of thoughts and findings from NAV experts and devotees around the world. We hope these insights will benefit you, too.
Alternatives to Using SELECTLATESTVERSION in Dynamics NAV 2017
Saurav asks, " Does anyone know of a replacement for SELECTLATESTVERSION in NAV 2017?
Here’s the issue, the customer is consuming a web service that returns a value in the database. Up until Dynamics NAV 2009, it used to show what the current value is, but after upgrading to NAV 2017 when they use SELECTLATESTVERSION it returns the current-1 value.
Points to Note:
- Customer doesn’t want to use SELECTLATESTVERSION
- I have already tried disabling data caching on the web services Service Tier
Any thoughts on what else I can try?"
Kyle asks, “What happens if you put a COMMIT right before the SELECTLATESTVERSION?”
Saurav replies, “It works with SELECTLATESTVERSION, but they don’t want this solution as they have to change it in so many places.”
Kyle responds, “The only other thing I can think of would be to make use of the CURRENTTRANSACTIONTYPE command, so you can tell it how you want the transactions layered.”
Transaction Type Behavior
This is a read-only transaction. Modifications cannot occur within the transaction. All read operations are performed with READ UNCOMMITTED locking. Therefore, no locks are added and locks that are added by other sessions are not honored. This means that the transaction may read uncommitted data.
For more information about READ UNCOMMITTED locking, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL) in the MSDN Library.
This is a read-only transaction. Modifications cannot occur within the transaction. All read operations are performed with REPEATABLE READ locking. Therefore, shared locks are added on all data and are maintained until the end of the transaction. This prevents other transactions from modifying any rows that have been read by the current transaction.
For more information about REPEATABLE READ locking, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL) in the MSDN Library.
This is an update transaction. Modifications can occur within the transaction. All read operations are performed with READ UNCOMMITTED locking until the table is either modified by a write operation or locked with the LOCKTABLE Function (Record). From this point until the end of the transaction, all read operations are performed with UPDLOCK locking.
For more information about UPDLOCK locking, see Table Hints (Transact-SQL) in the MSDN Library.
This transaction type improves concurrency for all tables that users access within the transaction by delaying locking as much as it can. However, the disadvantage is that you must know when to lock the tables for the required transaction behavior.
This is the default transaction type if you have not specified a type with the CURRENTTRANSACTIONTYPE Function (Database).
This is an update transaction. Modifications can occur within the transaction. All read operations are performed with REPEATABLE READ locking until the table is either modified by any write operation or locked with the LOCKTABLE function. From this point forward, all read operations are performed with UPDLOCK locking.
This transaction type provides full transaction isolation from the start of the transaction, regardless of the lock status of tables that users access within the transaction.
Bill W asks, “So the data is being updated directly in SQL? What process is that? It's not clear to me what is going on. Usually this is only a problem if something is changing data in SQL and it's not the NST. As far as I know there is no way to completely shut off the data caching on the NST.
- A job that reads the data out of the table again so the NST updates the data cache
- Change the process so the NST is the one updating the database.”
Saurav replies to Bill, “No. If data was modified by the Windows client and requested by Web service, it used to show old results.”
Saurav figures it out, noting, “Finally, after all R and D, the setting that need to be updated in the config file is set “DisableDataCache” to TRUE. It was simple to replicate but took some time to figure out the solution. Anyone who wants to replicate this let me know. It was interesting. Thanks all.”
Bill W follows with, “Good to know! That setting has been around since NAV 2016, too. It'd still be nice to get to the root cause of why the cache isn't updating. Hopefully, they don't see any performance related issues.”
If you are interested in NAV development, be sure to see our collection of NAV Development Blogs.
Read the "How To" blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.