Database deadlock detection and monitoring in Dynamics NAV 2017

Sometimes a programming error in C/AL can lead to users experiencing errors due to database deadlocks. A database deadlock can occur when two sessions try to update the same data, and are acquiring database locks in different orders.

For example, let’s imagine two code units 10001 and 10002, which both do updates to records in tables A and B. In code unit 10001, we first update table A and then table B. In code unit 10002, we first update table B and then table A. Updates in both code units are included into transactions.

Now, there are two user sessions (session 1 and session 2) that connect to code units 10001 and 10002 (session 1 connects to code unit 10001 and session 2 connects to code unit 10002).

A deadlock can occur when the following timing of events happens:

Time Session 1 Session 2 Remarks
1 Start transaction
2 Start transaction
3 UPDATE Table A
SET …
WHERE CustomerID = 1
Session 1 now holds an exclusive lock on the row with CustomerID = 1 in table A.
4 UPDATE Table B
SET …
WHERE CustomerID = 1
Session 2 now holds an exclusive lock on the row with CustomerID = 1 in table B
5 UPDATE Table B
SET …
WHERE CustomerID = 1
  Because session 2 holds an exclusive lock on the row in table B, session 1 must wait for session 2 to commit.
6    UPDATE Table A
SET …
WHERE CustomerID = 1
Because session 1 holds an exclusive lock on the row in table A, session 2 must wait for session 2 to commit. Both sessions are now waiting for each other, and we have a deadlock.

 

SQL Server will detect deadlocks and will dissolve them by rolling back one of the transactions.

As a new feature in Dynamics NAV2017, you can configure that information about database deadlocks is written to the Windows Event Log. If database deadlock logging is enabled, Dynamics NAV2017 will log the following:

  • Dynamics NAV Server instance on which the event occurred. 
  • Dynamics NAV user account that ran the transaction that caused the event. 
  • Dynamics NAV object in C/AL that ran the transaction, such as a page or report. 
  • ID of the object that was run. 
  • C/AL function that ran the transaction that caused the event. 
  • Deadlock report that was received from SQL Server. 

You can read more about the feature in the Dynamics NAV 2017 developer and IT-pro documentation under Administration > Monitoring Microsoft Dynamics NAV Server > Monitoring SQL Database Deadlocks.

For more information about SQL Server deadlock detection and termination, see https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx.