Recently, one of our distribution customers experienced dead-lock during posting of Sales order or purchase order. Because of this dead-lock their operation was delayed, one need to wait for another person to complete the posting, if the user try to force through the Posting part by clicking the Post action button, they will receive “Table xxx” is locked by another user. “Dead-lock” affects their sales, as the customer did not want to wait the user to finish posting the sales order.

What “dead-lock” means in Navision? Dead-locks can prevent users from completing tasks in the Dynamics NAV client. A dead-lock occurs when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server handles dead-locks by terminating and rolling back transactions that started after the first transaction.

In Dynamics NAV 2017, there is a new feature where the system logs this “dead-lock” and captures information related to this dead-lock, such as what is the query or scenario could trigger the block or how long the process happens. To enable the logging, “Enabled Dead-lock Monitoring” field on the Dynamics NAV Administration – database tab has to be “ticked”.

After Enable Dead-lock monitoring has been setup, and the dead-lock happens, you can view the information related to the dead-lock process.

Open event viewer (Windows feature):

Click Application and Services Logs – Microsoft – Dynamics NAV – Server – Admin

Then, filter current log by keying in “705” in the filter box as shown in the image below

Once filtered, you can find the log time when the dead-lock occurs, example as image below.

The red box provides information of the dead-lock process and the paragraph after the timing is the query make the dead-lock process.

If you have many users doing transaction postings or reads & writes at the same time, dead-locks is inevitable. So how do we minimize or mitigate this situation.

1. Have a faster computer server and faster hard-disk would definitely help as the processing would be faster and the release of the lock from the user would be process faster.
2. The other frequently overlook item is the type of the hard-disk which translate to the speed of the hard-disk. Talk to your Infra team and determine if the hard-disk is adequately fast enough.

1. Having an Enterprise grade Database system, such as Microsoft SQL DB would greatly help as well, so if possible, upgrade to at least SQL v2012, v2016 or v2017 would be even better.

Regular SQL DB maintenance such as add SQL indexes, reorganize SQL index once a week, rebuild SQL index once a month and add right index will minimize the occurrence of the dead-lock.