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

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

Rate This
  • Comments 5

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
 

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • Just wondering... Looking at the C/AL code differences between 2009 and 2013 it looks that this can be applied in 2009 version...not sure if there are some technical limitations in 2009 version ?  

  • As mentioned by Igor, the change in NAV2013 looks to be workable with NAV2009, can the NAV Team comment on this?

  • Yep; the changes can easily be Applied to NAV2009. The main trick is to sort the sales lines by Type (and number) when posting in codeunits 80 and 90. This, on the other hand, means that you need to take ekstra care when extra sales lines are inserted at the end at run-time - e.g. the invoice rounding line. Also, take note of inventory posting that can be set up to post cost directly, which sabotages this effort. Apart from that, it should be straight forward.

  • Look at www.microsoft.com/.../Case_Study_Detail.aspx

    Locking issue can be resolved for all types of transactions.

  • Hi All,

    Did someone applied this trick in NAV2009 ?

Page 1 of 1 (5 items)