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.

Comments (10)

  1. Paquirrin says:

    “In code unit 10001, we first update table A and then table B. In code unit 10002, we first update table A and then table B”
    Shouldn’t it be:
    “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 “

    1. Yes, you are right. We will fix that right away. Thanks for pointing that out

      1. navteam says:

        Corrected now – thanks, Paquirrin!

  2. René Gubler says:

    Do you implement this in older NAV Versions? What is the timeline?

    1. Dear René Gubler,

      No, we have no plans of implementing this in older versions of NAV.

      Best wishes,
      Kennie Nybo Pontoppidan

  3. Alex says:

    I really appreciate the feature, but as far as I can tell, you are only logging the side of the person whose transaction is being terminated, not the one who is causing the other end of the deadlock. Are you planning to change this? (Of course I will be able to view the deadlock report from SQL Server, but it won’t tell me which NAV object was causing it.)

    Furthermore, are you planning to include blocks in the future as well? E.g. if you have one users locking one record for a longer duration and another user wants to apply a lock to it in the meantime. I think with these additions, this feature would be really awesome. (Though I am not sure, if it will be feasible to enhance it in such a way… and it might also slow down the overall performance.)

    1. Hi Alex,

      Thanks for your input.

      Currently, we have no easy way to capture the AL specific details of the winning transaction from the session of the deadlock victim.

      I’m not sure I understand your proposal about “blocks in the future.” We do have a page, where you can monitor (current) database locks, see
      https://msdn.microsoft.com/en-us/dynamics-nav/monitoring-database-locks

      1. Alex says:

        Hey Kennie,

        Yes – this is basically the feature I was referring to. However, if I am not mistaken, the feature is only showing me current locks and their status. E.g. I see that currently there is one “granted” lock and one “waiting” lock, but I am not able to generate a log of any termination happening here. So if I did not check the issue right away, the information is lost. That’s why I was hoping to have both – deadlocks and blocks – logged in the same way, if they cause any kind of termination for another session.

        1. Hi Alex,

          Waiting for locks is not a bad thing (as seen from the database). It is just a mechanism to ensure ACID properties. Only in the case of deadlocks, is this generating something bad for a user – the transaction being terminated. It is not possible to deduce up front that a lock will result in a deadlock. If you want to track locks live as they appear, you can setup an extended event trace on SQL Server (this is what we do in NAV) to listen on this.

          See an example on how to do this here:
          https://msdn.microsoft.com/en-us/library/bb677357.aspx

          1. Alex says:

            Hi Kennie,

            Thanks for your reply. Currently I am using extended events in a NAV 2015 environment. I set up two, one for blocks and another for deadlocks. That is actually the reason for my question here. I was hoping that this feature could make both extended events obsolete – not just one of them. 🙂

Skip to main content