• +44 (0)20 3772 1081
  • sales@sqlmantratools.com

Tri-state Locking to Improve Concurrency of Business Central

Recently in BC23, Microsoft has slipped through a feature called “Enable Tri-State locking in AL”. Having this feature is like hitting a gold mine when it comes to performance improvement, especially increasing the concurrency of the Business Central system. There is relatively less advertisement about this feature, compared to the benefit it brings, from Microsoft. For years we have struggled with the “pessimistic locking” regime of Dynamics NAV and Business Central. This even goes as far as Navision (the predecessor of Dynamics NAV and Business Central). This locking behaviour will lock tables and rows which should not be locked in reality, leading to lock escalations, blocking and deadlocks as concurrent sessions run into each other’s locked ranges in tables are indexes.

There were many failed attempts to tackle this problem by Microsoft / Navision in the past. “Always rowlock” settings in the database tab is an example. Some of you may remember this setting from those days (see below).

Always Row Lock setting in Dynamics NAV & in Navision

The fundamental problem is when a row is modified or deleted any subsequent data retrieval from the same table will have a “UPDLOCK” hint added to the select query, while the transaction is active. Due to the blunt nature of this hint which is added by the CSIDE (API between NAV application server and SQL Server), it leads to significant locking. This locking regime is honestly an over kill. As performance tuning experts in the Dynamics industry, for the past 25+ years we have to face the full brunt of these issues to deliver better, faster systems to accommodate more concurrent users to the industry and to our clients.

The Tri-state locking change has finally addressed the root cause of this problem, selectively issuing the “UPDLOCK” lock hint while reading the rows. There is an article on the Microsoft website, explaining the change. This is nicely summarised in the diagram below. It is fairly easy to activate this feature through “Feature Management” page in Business Central. This feature will be automatically enabled in BC25 onwards. We will encourage all Business Central customers to enable it for better performing Business Central System. Wish we could have had this earlier.

Table locking in Business Central with and without tri-state locking

How to enable tri-state locking in Business Central

SQL Mantra Tools logo

SQL Mantra Tools monitors Dynamics NAV and Business Central performance 24/7, in the Microsoft cloud and on-premises, and pinpoints the root cause of every performance problem: the AL and C/AL code, the named Business Central or NAV user and the SQL code behind each slow query, blocking event and deadlock, plus network speed by user and site. Ask AI for Fix then turns each incident into a root cause and a ready-to-use AL code fix. This is what sets our software apart.


SQL Mantra Tools also analyses your SQL Server's key setup parameters, resources including key Dynamics NAV and Business Central setups, checks them against best practice and recommends the steps to remedy any gaps.


Our maintenance routines proactively keep Dynamics NAV and Business Central databases free from performance degradation, optimising their size and minimising database growth.


We support and tune every version of Navision, Dynamics NAV and Business Central, SaaS and on-premises, whether or not Microsoft still supports that version.