How to detect locking order for a NAV process
The nature of a deadlock is that two processes lock resources in different orders. Deadlocks can in theory be eliminated by ensuring that all processes always lock resources in the same order. This document describes how to determine the locking order of a process in Microsoft Dynamics NAV.
Note, that I mention locking of “resources”. For most of the time, this means placing locks on a table, but it could as well mean placing locks on different parts of the same table, or of different indexes in the same, or two different tables. But then we are into micro-tuning, so for this post, when I say “Resource”, I mean “Table”.
What is a deadlock:
A deadlock will return an error message which specifically says that your activity was deadlocked. The deadlock error-message will read:
“Your activity was deadlocked with another user modifying the [xyz] table.
Start again.”
A blocking chain is a situation where a client hangs (becomes unresponsive) until a resource becomes available. In common language you could describe such a situation as a deadlock situation. But technically, this is not a deadlock, since the situation will be resolved eventually. In other words, to any user, an infinite block may look like a deadlock. But if you have deadlocks, then by definition, your users will have deadlock-error messages like the one shown above. One of the first steps in troubleshooting any kind of locking or blocking is to identify exactly what type of problem you have. So always make sure to first find out whether you are dealing with blocking chains, deadlocks, or other issues, since selecting the best troubleshooting methods depend on what exact issue you are looking at.
This post shows how to determine the locking order of a process in NAV. Knowing the locking order of various processes can help identifying potential deadlocks. It can also help in cases where you know that two or more different processes are causing deadlocks, by showing the locking order of each of these processes.
Pre-requisites:
The tool described here is part of the Performance Troubleshooting Guide, which is available on PartnerSource (partner login required). The tools described here require a NAV Partner license.
The SQL Server Performance Troubleshooting Guide, includes an object called “Client Monitor.fob”. To begin, import this into the database which contain the processes that you are troubleshooting. It does not have to be a live database – a stand-alone copy of a live database is all you need. In this scenario you are examining locking orders of processes which can be done as well in your office, on a copy or test-version of a database, as in a production environment.
To use “Client Monitor.fob”, follow these steps, after importing it into the database:
1. Start Client Monitor (Tools -> Client monitor -> Start). Before starting it, go to the Options tab and de-select “Include Object table activity”, and select all the options under “Advanced”.
2. Run the processes for which you want to detect the locking order.
3. Stop “Client Monitor”.
4. Run form 150025 “Transactions” which was imported in “Client MOnitor.fob”. This will take a while, while it collects the information that was collected by the Client Monitor. When it opens, it will show you one line for each transaction.
5. For each of the lines shown in this form, click on Transaction -> “Locking order” to see the locking order of each transaction.
This shows you not just the locking order of each transaction, but you will also see the C/AL code that places the lock. If you compare this information with knowledge of what tables and processes are involved in the deadlocks you are troubleshooting, it can help you decide where locking orders need to be changed.
What locks a record?
NAV will automatically place a lock as soon as any update command is used (INSERT, MODIFY, DELETE), even if the C/AL command LOCKTABLE is not used. On SQL Server, Navision will only lock the record.
The C/AL command LOCKTABLE in itself does not lock anything. It only puts NAV into locking-mode. Navision will then lock any records that it accesses. This is why the places in C/AL code that place locks are typically not LOCKTABLE-commands, but for example a FIND(‘-‘)-command that comes after a LOCKTABLE command.
What to do next:
Once you have determined that two different processes have different locking orders, you must decide on a locking order. There are no complete guides to which order should be used, but if you look for the C/AL command RECORDLEVELLOCKING in codeunit 80, you can see that this codeunit locks a number of tables in a certain order. At least for tables that are included in this order, it makes sense to stick to this order. For tables which are not included here, you must make your own decisions. Once you have decided on a locking order, you can use one of the following tactics to change it:
Avoid locking:
Some times it is not necessary for a process to access certain tables. For example, the tool may show that table 5765 – “Warehouse Request” is being locked. If a customer is not using warehousing, then the code that locks this table can some times be remarked.
Lock sooner or later:
One of the most common ways to change locking order is to move a lock up so that it happens sooner in the process. For example, lets say you have this line of code:
SalesOrder.INSERT;
And you know that later on in the code, you will be locking one of your own tables. To change the locking order of these two tables, add:
YourOwnTale.LOCKTABLE;
Before the line where you lock the Sales Order table.
Master lock (semaphore):
You can decide that all processes must lock a certain record before they lock anything else. In this case, only 1 process will run at the time. This will effectively eliminate any deadlocking, but it will also reduce concurrency, and can lead to blocking chains instead.
As you can see, two of the tactics above (lock sooner, and Master lock) may reduce the risk of deadlocks. But on the other hand they can also increase blocks, because (more) resource(s) get locked sooner. So always make a total assessment of the problem that deadlocks cause. Some times it may be better to have a weekly deadlock, but optimistic locking, than begin to rearrange locks and cause more blocks.
