·
2 min read

G/L Entry Table Locking Redesign in Microsoft Dynamics NAV 2013

Do you or your customers have peak hours when all the sales orders and invoices need to be posted at the same time? Or do you have large numbers of postings that need to be run without blocking other users? I bet the answer is yes. If so, then you may also have experienced your screen freezing for several seconds – minutes even – until the order is processed. “Try again later” is also a well-known option.

To provide a better user experience and to enable more users to leverage the possibilities of Microsoft Dynamics NAV, we have redesigned how the General Ledger (G/L) Entry table is locked during the posting process.

The implementation of Microsoft Dynamics NAV application was, for historic reasons, primarily designed for Microsoft Dynamics NAV Classic Database Server, but it has been adapted and deeply enhanced to also run on SQL Server. Microsoft Dynamics NAV Classic Database Server uses table locking, and the locking order and use of semaphores are designed to avoid deadlocks in a table locking scenario. With Microsoft Dynamics NAV 2013, the Classic Database Server is retired, which allows us to fully benefit from SQL Server’s row level locking. We decided to focus on a few key scenarios, as they involve most users:

  • Posting a Sales Order (codeunit 80)
  • Posting a Purchase Order (codeunit 90)

These enhancements to G/L posting, combined with the job queue and background posting will open up more flexible usage of Microsoft Dynamics NAV and will lead to more efficient users and a better experience in peak hours.

Setup and Use of the Redesigned G/L Posting

The new locking schema is enabled by default. Do you want to still use the legacy one? That’s very easy:

  1. In the Search box, enter General Ledger Setup, and choose the related link. The General Ledger Setup window opens.
  2. On the General FastTab, locate the Use Legacy G/L Entry Locking field. This field controls the behavior of the posting routines as far as locking is concerned.

Note: The new behavior is automatically turned off if the Automatic Cost Posting field is selected in the Inventory Setup window.

What Has Been Changed

Of course, you can make a comparison of codeunits 80 and 90 between previous versions of Microsoft Dynamics NAV and Microsoft Dynamics NAV 2013. But even better, you can see a graphical representation of the changes.

In codeunits 80 and 90, we were locking the G/L Entry table (as a semaphore) quite early during posting, thereby locking other users out from posting at the same time. This lock has been moved to a later stage in the posting process. With those changes, our estimates are that:

  • Microsoft Dynamics NAV 2009: G/L is locked >90% of the time during a typical sales or purchase post.
  • Microsoft Dynamics NAV 2013: G/L is locked <10% of the time during a typical sales or purchase post.

-Tomás Navarro Casbas
 

navteam

Related posts