Identify table locks in Dynamics NAV
The identification of table locks in Dynamics NAV has completely changed since Dynamics NAV 2013 compared to the Navision Classic Client. The way Dynamics NAV users connect to the SQL database has been restructured by the RTC Client. Users connect to the NAV service tier from the Windows client, web client, Sharepoint client or web services. The new so-called "three-tier architecture" separates database, server and client tiers.
The user account that runs the NAV service tier is the only one that actually connects to the SQL database. There are many good reasons for this:
- Dynamics NAV Server uses an ADO.NET interface, which is a data access layer that supports SQL Server connection pooling. This simplifies the deployment of the Microsoft Dynamics NAV three-tier architecture for deployments where the three tiers are installed on different computers. The Windows Communication Framework (WCF) is used as the communication protocol.
- It improves security, since in the SQL database there is no need to create a SQL user with login name for each of the required NAV users. As a result, security is simpler, since there is no need for an improved security model to previous versions (client-server connection).
Great stuff! However, a side effect is that it is difficult to see which SQL Server connection (SPID) is assigned to a NAV session. This is a problem when trying to determine a Windows user to a table locking/blocking issue.
In the days of the classic client where you could see the active database sessions and blocking information along with the ability to terminate sessions if necessary are unfortunately history due to the new architecture.
Since customers keep asking us about the problem of identifying table locks in Dynamics NAV and if we didn't have a tool or solution for this problem, we developed a solution. Our first approach to a solution was our V8 NAV SQL Studio. However, the architecture of the software was suboptimal when installing Dynamics NAV service animals on different computers.
Monitor Microsoft Dynamics NAV server events:
For better understanding we would like to briefly explain the difference between Locking, Blocking and Deadlocking for all non SQL Server specialists. Locking: Locks are a mechanism used by Microsoft SQL Server to synchronize simultaneous access to the same data by multiple users.
Before a transaction retrieves a dependency for the current state of data, for example by reading or modifying the data, it must protect itself from the effects that may result if another transaction modifies the same data.
Blocking:
A BLOCKING occurs when two connections need access to the same data item at the same time and one connection is blocked because only one connection can have access at any given time.
This message then appears in NAV:
Microsoft Dynamics NAV
—————————
The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------
Deadlocks:
DEADLOCKS occur when two tasks permanently block each other because each of the tasks maintains a lock on a resource needed by the other task.
Consequently, transaction A cannot be completed until transaction B is completed. However, transaction B is blocked by transaction A.
This message then appears in NAV:
Microsoft Dynamics NAV
—————————
The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------
Locking is an integral part of Microsoft SQL Server to ensure parallelism and physical integrity of each transaction. Blocking is bad when a connection/transaction waits unnecessarily for a long time, and deadlocking is a phenomenon that should never occur.
V8 Search XE - Detect and fix deadlocks and blockages in Dynamics NAV Server.
Event tracing in V8 Search XE provides detailed information about what occurs on the Microsoft Dynamics NAV server when users are working with Microsoft Dynamics NAV and blocking or deadlocks occur. All data on specific Dynamics NAV trace events is captured in a V8 SQL Server database. This can help you identify and analyze issues or conditions affecting Dynamics NAV / SQL Server performance.
Identify table locks in Dynamics NAV. With V8 Search XE event tracking, you can dynamically monitor Microsoft Dynamics NAV servers without restarting the server or Microsoft Dynamics NAV clients.
You can use the V8 Search XE to track, for example, the following operations on Microsoft Dynamics NAV Server instances and the SQL Server:
- Execution of SQL statements from Microsoft Dynamics NAV Server.
- Execution of NAV C/AL functions.
- Execution of Microsoft Dynamics NAV reports, queries and XMLports.
- Process number, status, locks and commands (TSQL and C/AL or AL) executed by the active users.
- Locked objects as well as the type of locks set up.
- Full Dynamics NAV SQL trace of a locking process (SPID | DYNAMICS NAV USER).
- Full Dynamics NAV SQL trace of active users by total waiting time (SPID | WAITTIME).
Which Dynamics BC / NAV Windows user locks the table?
We will be happy to personally answer any further questions you may have on this topic. Simply contact us via our contact form or by e-mail to info@dynamicsproject.com!
Your team from DynamicsProject.com
English 
German