One important aspect of the design of an integrated system such as NAV, that is often overlooked until it rears its ugly head after the system goes into production, is the issue of Locking. Locking occurs when one process has control of a data element, record, or group of records (in other words, part or all of a table) for the purpose of updating the data within the range of the locked data and, at the same time, another process requests the use of some portion of that data but finds it to be locked by the first process.

If a deadlock occurs, there is a serious design flaw wherein each process has data locked that the other process needs and neither process can proceed. One of our responsibilities as developers or implementers, is to minimize locking problems and eliminate any deadlocks.

Locking interference between processes in an asynchronous processing environment is inevitable. There will always be points in the system where one process instance locks out another one momentarily. The secret to success is to minimize the frequency of these and the time length of each lock. Locking becomes a problem when the locks are held too long and the other locked-out processes are unreasonably delayed.

One might ask, what is an unreasonable delay? For most of the part, a delay becomes unreasonable when the users can tell that it's happening. If the users see stopped processes or experience counter-intuitive processing time lengths (that is, a process that seems like it should take 10 seconds actually takes two minutes), then the delays will seem unreasonable. Of course, the ultimate unreasonable delay is the one that does not allow the required work to get done in the available time.

The obvious question is how to avoid locking problems? The best solution is simply to speed up processing. That will reduce the number of lock conflicts that arise. Important recommendations for improving processing speed include the following:

• Restricting the number of active keys, especially on the SQL Server
• Restricting the number of active SIFT fields, eliminating them when feasible
• Carefully reviewing the keys, not necessarily using the factory default options
• Making sure that all disk access code is SQL Server optimized

Some additional steps that can be taken to minimize locking problems are as follows:

• Always process tables in the same relative order
• When a common set of tables will be accessed and updated, lock a standard master table first (for example, when working on Orders, always lock the Order Header table first)
• Shift long-running processes to off-hours or even separate databases

In special cases, the following techniques can be used (if done very, very carefully):

• Process data in small quantities (for example, process 10 records or one order, then COMMIT, which releases the lock). This approach should be very cautiously applied.
• In long process loops, process a SLEEP command in combination with an appropriate COMMIT command to allow other processes to gain control (see the preceding caution).

Refer to the documentation with the system distribution in the NAV forums.

With NAV 2017, Microsoft has introduced a new feature that allows you to view the current locks in the database. You can access this page from the Development Environment by selecting Tools | Debugger | Database Locks.... The following screen will be displayed:

For more details refer to the following Help section: Monitoring SQL Database Locks.