Record-level locking in the SQL Server option for Microsoft Dynamics NAV

If you are designing a system for high throughput and want to minimize blocking, it's important to know in advance exactly what kind of locking behaviour to expect.

On SQL Server, Microsoft Dynamics NAV uses record-level locking, as opposed to table-level locking on the native database server. In reality, SQL Server can lock a little bit more than the individual records you want to lock: It can also lock the next and the previous records. This happens when SQL Server applies a range-lock.

This post describes how and when a range-lock can occur. It applies to a normal lock, regardless of the "Always Rowlock"-setting in NAV, and lock escalation where SQL Server can escalate a record-level lock to a table-lock - these are seperate issues.

Here are some examples:

Table 7 "Standard Text", contains the following records:
Code Description
MD    Monthly Depreciation
SC    Shipping Charge
SUC  Sale under Contract
TE     Travel Expenses

Table 7 is used here, because it is the simplest possible table.

The following C/AL code in NAV will lock just one record (SC - Shipping Charge):

OnRun()
StandardText.LOCKTABLE;
StandardText.GET('SC');

IF NOT CONFIRM('Continue?') THEN
  ERROR('Stopped.');

You can confirm this by running it from one client, then open another client and try to update the records in the table.

Now, consider this C/AL code:

StandardText.LOCKTABLE;
StandardText.SETFILTER(Code,'S*');
StandardText.FINDSET;

You might expect that it will lock the two records that fall within this filter (S*): SC and SUC. But this is where SQL Server locks a bit more: It will also lock the records around this filter (MD and TE). This is because as soon as the lock covers a range and not just one individual record, SQL Server has to protect not only the locked records, but also the range itself. In this case, the range covers 2 records, and SQL Server prevents anyone else from inserting new records in this range. It is SQL Server's way to guarantee that the range stays at these 2 records.

Depending on your C/AL code, some times SQL Server protects just the beginning, and some times just the end of the range, and some times both the beginning and the end. So when you lock a range, you should assume that that both the record just before, and just after, will also get locked.

Here is an example from a more real situation. Imagine you have a process to update sales order 2002 (posting / releasing or any other process). You may have C/AL code to lock the sales lines, like this:

SalesLine.SETRANGE("Document Type",SalesLine."Document Type"::Order);
SalesLine.SETRANGE("Document No.",'2002');
SalesLine.LOCKTABLE;
SalesLine.FINDSET;

In this case, also the lines for order 2003 will get locked! So even when users are working on their own documents, they can still end up blocking each other.

When this is causing problems, what methods can be used?

The simple answer is, to make sure that users don't work on records that are just next to each others. One way would be to insert "ghost" records between each normal record. Another way, is to change the sorting. In the example above, if you had added this line at the beginning:
SalesLine.SETCURRENTKEY("Document Type","Sell-to Customer No.");

Then order 2003 will not get locked, because with this sorting, it is not the next record.Or you can design a solution that works in more random areas of a table. Using Number Series means that active documents are often in the same area of the table. If you use random numbers, or GUID as primary keys, then the active documents would be spread through the whole table, and the risk of users updating records next to each other, would be smaller.

These are just some methods to consider, but the main aim would be to spread activity on a contented table to avoid hot-spots in that table.

Open-ended ranges

Locking the last record is used in a number of places in the NAV application, including the posting routines, which normally lock the last record in the entry table. When the record you are locking is the last one in the table, then the "next record" is anywhere between the last record and infinity. So in this case, the range that is locked can become much larger.

If we go back to the Standard Text table and run this C/AL code:
StandardText.LOCKTABLE;
StandardText.FINDLAST;

Then the locked range will include any possible record after the last one (TE - Travel Expenses), so it will prevent from inserting any new records that would come after TE.

 

If designing an application from scratch, then you may want to take this behaviour into consideration. If changing an existing application, then in some cases it will require major re-design. So always also consider other ways to improve performance. There may be easier ways to resolve blocking problems.

  

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Comments

• Anonymous
  February 19, 2008
  Something I do not find much documentation about is the "Always Rowlock" option. What is exactly the effect of this option and when is it recommended or not recommended ?

• Anonymous
  February 20, 2008
  The nature of a deadlock is that two processes lock resources in different orders. Deadlocks can in theory

• Anonymous
  February 21, 2008
  The nature of a deadlock is that two processes lock resources in different orders. Deadlocks can in theory

• Anonymous
  February 27, 2008
  About "Always Rowlock", the simple answer is, that whenever NAV locks anything, it adds WITH ROWLOCK hint. Without it, we let SQL Server decide what kind of locks to use. That's the default. Rowlocks require memory to maintain, so some times, SQL Server will convert many rowlocks into one table lock when it thinks that it can use its memory better in other places. The ROWLOCK hint will prevent this. So, if you activate "Always Rowlock", you may get fewer table locks, but SQL Server may need more memory. It's impossible to say in general, how it will affect each individual system. If you think you have quite a lot of memory, then the only way to see the effect, is to turn it on, and see if the system works better. I'm afraid that I don't know of any better general rules, when to advice to turn "Always Rowlock" on or off.

• Anonymous
  October 28, 2008
  Few new articles about NAV on SQL released by experts: Lars Lohndorf-Larsen article "Record-level