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.
