Top Two Problems for Dynamics NAV Classic Client: Table Locks and Time

Two of the biggest complaints I hear from clients who are still on NAV versions prior to NAV 2013 are the headaches caused by table locks, and the time required to post sales orders. If you are one of those people suffering from either (or both) of these issues, keep reading, there is hope. With NAV 2017 – provided your custom code is written properly – the chances of either of these being a problem are pretty close to zero.

Dimensions

Prior to NAV 2013, each dimension to be recorded for a document had to be written to a table as a single record. Therefore, if you have two header dimensions and three line dimensions, a sales order that had 10 lines would create 32 records. When the sales order was posted, it would create another 32 records for the sales shipment, and another 32 records for the sales invoice.  Moreover, to add to the processing, the original records with the sales order have to be deleted when it is deleted during the posting. Therefore, for that one document, you end up with 128 records processed.

From NAV 2013 and forward, only one number, called a Dimension Set ID, is written, and it is written only once to the header. Each line can have a different dimension set, so you get one number written to each order line. If we use the same example as above, that’s one dimension set ID for the header, one for each line, and it is stored on the existing records. No additional records are created. When you post the document, the dimension set ID is transferred in one fell swoop to the posted documents and lines. Again, no additional records. When the sales order is deleted, there are no external records to remove. Imagine the amount of disk space that saves. 

Depending on the extent to which dimensions are used, we have seen a 10-30% decrease in database size during upgrades simply due to the new dimensions management restructure. That is something to consider if you have a rapidly growing database. To see a one-terabyte database shrink to 700 gigabytes in an upgrade is a reason to celebrate.

Dimensions, Table Locks, and Slow Performance

Table locks are issued in code with the LOCKTABLE function. This prevents multiple users from writing to the same record at one time. This collision of data would create all sorts of issues in NAV, so NAV prohibits it.  When posting routines are slow, typically due to disk operations (like writing dimension records), there is a greater opportunity for a user collision to occur. When this does happen, a message is sent stating that another user has the table locked, and the operation cannot be performed. It is sort of like a revolving door. The faster it moves, the more people (or transactions, in this analogy) can enter. However, if anything slows down your revolving door (processing), your transactions are likely to hit a closed door. By using the new dimension set ID design, NAV has increased its processing speed, which provides a decreased opportunity for those user collisions to occur.

Microsoft Dynamics NAV Going Retro

Think back a few years to the introduction of NAV 2009. Microsoft did not do us any favors when they took away the ability to see what job was locking a table back in NAV 2009. Without the ability to see the issue quickly, we had to rely on SQL traces and things of a far more technical and time-consuming nature to determine the cause of the lock. Fortunately, Microsoft seems to have relented, and yes, NAV 2017 once again includes the ability to see who is causing a job to be locked.

To illustrate, I have created a lock on the Customer table. Then I have gone into a customer record, and tried to update the name.  As you already know, that is not possible with a table lock, and so the error below is given:

Figure 1 - Error Message on Customer Card

Now in this example, of course, I know who created the lock. However, imagine in a database of 100 active users this occurs, and perhaps even on the General Ledger Entry table. There is no easy way in versions from NAV 2009 through NAV 2016 to find the cause of the lock. Even in versions before 2009, when the lock occurs and you can find out who it is, it is a huge headache to resolve because the processing speed often makes this a recurring issue. In walks our hero, NAV 2017. 

In the NAV 2017 development client, simply choose Tools – Debugger – Database Locks.  Any, and all, locks that are currently found in the database are shown.  The image below shows just how simple it is to determine the cause of a table lock.

Figure 2 - Dynamics NAV 2017 shows the cause of the table lock

How do you end the lock? Upgrade your old database, and not only will you have less opportunity for table locks, but you’ll decrease the size of your database immediately when those dimension records are converted to dimension set IDs.