13th May 2015
NAV uses optimistic Concurrency for all the transaction except for posting. During posting, NAV locks the ledgers and sub ledgers, basically any table that has primary key of “Entry No.”
In Order for posting to work NAV has kept the posting process to as fast as possible. Most partners forget this import part during implementation and modification of the system.
Most of the big projects where the user count is greater than 50 and high transaction volume by the user, NAV system starts to show the weakness of its design. Users get locking error whenthey post a transaction and the system becomes unbearable. The client invests in time consuming sql tuning process that mitigates the problem to a bearable situation and it becomes long term struggle for the client.
MS has done some changes to improve concurrency in NAV. If you are on SQL and still using classic client please do an exe upgrade to the latest 2009 executables. MS has changed the changed the isolation level from SERIALIZABLE to REPEATABLEREAD. After you do the exe upgrade run the following sql command
UPDATE [$ndo$dbproperty] SET diagnostics = 4194304
This reduces the locks on table dramatically on sql server and increases concurrency in NAV.
Here is the MSDN Link.
This is just the start of the increasing concurrency in NAV.
The next step is redesign of some tables in NAV that are bottlenecks. The first table is the “No. series line” table. The “No. Series Line” is using throughout the entire application to grab the next Document Number, for example Order, Invoice No. So if there are two users that are creating documents, they will be locked by each other till they finish their transaction. This is not a major issue when users are creating one document, but for customers that create many documents at a time, the users will be locking each other constantly.
An example of this scenario, A company runs MRP at night splits the results into multiple batches. In the morning the buyers come and look at their batches and create Purchase Orders. I’ve worked with clients where they have more than 10 buyers that daily created purchase orders to replenish inventory.
One way to eliminate the locking on “No. series line” table to add a “User ID” to be part of the primary key. Codeunit “NoSeriesManagement” will need to be modified. In functionSetNoSeriesLineFilter
NoSeriesLine.SETCURRENTKEY("Series Code","Starting Date");
NoSeriesLine.SETCURRENTKEY("User ID","Series Code","Starting Date");
This is a fairly small modification that should be part of standard NAV. This adds additional setup for number series, but that can be automated. In future blog I’ll write about how to automate that as well. And also make it configurable, so that you can create on some number series and not others.
Here is a screenshot of how it will look once User ID is added to number series.