Optimized Locking in Dynamics 365 & Azure SQL

by HubSite 365 about Stefano Demiliani [MVP]

Citizen DeveloperDynamics 365Learning Selection

Unlock the potential of your databases with Azure SQLs new optimized locking feature, boosting performance and efficiency in Microsoft Dynamics 365 Business Cen

Unpacking Azure SQL's Optimized Locking Feature Utilized by Dynamics 365

Microsoft's cloud-based enterprise solution, Dynamics 365, is optimized through advanced Azure SQL database engine capabilities. One such feature is Optimized Locking, shown to significantly reduce lock memory and establish a more efficient environment for write operations. This feature aims to maintain fewer locks for large transactions, reducing lock escalations and increasing access to a table concurrently.

The Optimized Locking mechanism utilizes two principal components - Transaction ID locking and Lock after Qualification (LAQ), without the need for additional configuration. Unique identifiers label transactions in the model, enabling an effective single lock system replacing numerous key or row identifier locks. Additionally, the LAQ mechanism evaluates queries on the row's latest committed version without acquiring a lock, leading to improved concurrency.

This beneficial feature currently has limited regional availability, including West Europe, UK South, Canada Central, Brazil South, and West Central US. Within these regions, optimized locking is activated by default in Database Management Systems, enhancing performance and efficiency.

Optimized locking profoundly depends on two other database features: Accelerated Database Recovery (ADR) and Read Committed Snapshot Isolation (RCSI). Both features are enabled by default in the Azure SQL Database, verifying the optimized locking capability. If we were to visualize this process, imagine applying an exclusive lock on the transaction ID instead of the row's key. Thus, the robustness of optimized locking in Azure can't be disabled (without a support ticket) and isn't available for on-site SQL servers.

Applying Improved Azure SQL in Dynamics 365

Dynamics 365 leverages the repeatable read isolation level, meaning data viewed during a transaction is ensured to remain unchanged until the transaction's conclusion. New rows may be altered during concurrent transactions, but these adjustments won't be visible in the second selection.

To demonstrate, we can run a piece of SQL code or compare a table after a minute delay between reads. By repeating this exercise while changing the database transaction isolation level to Read Committed, the software reveals that an update executed from the second session is immediately executed amidst the query of the first session.

While the advantages of utilizing this Azure SQL locking feature aren't instantly visible in Dynamics 365, the future holds promise. With the upcoming 2023 wave 1 update (v22), developers will access a new method for setting transaction isolation level via AL code. Coupled with Azure's advancements, these updates are bound to unlock massive improvements in performance and lock handling in the cloud-based applications available in Dynamics 365.

Expect noticeably better lock handling in different cloud-based applications and for Dynamics 365 to reap significant benefits in the near future.

Read the full article Dynamics 365 Business Central, Azure SQL and Optimized Locking

Dynamics 365 - Optimized Locking in Dynamics 365 & Azure SQL: A Guide

Learn about Dynamics 365 Business Central, Azure SQL and Optimized Locking

Microsoft offers a powerful set of tools and solutions to infuse businesses with productivity and efficiency. One such offering is Dynamics 365 Business Central, a comprehensive business management tool that integrates with Azure SQL, a relational database service in Microsoft's cloud. In our previous discussion, we have covered the concept of Optimized Locking, a new feature in Azure SQL that significantly decreases lock memory and the number of locks concurrently necessary for writes.

Optimized Locking enables large transactions to possess fewer locks due to its specific operation mechanism. It uses two main components: Transaction ID (TID) locking and Lock After Qualification (LAQ) to handle concurrent data modification requests. TIDs are unique identifiers for each transaction, allowing Azure SQL to only lock the specific TID against multiple key or raw identifiers. Concurrently, LAQ enhances concurrency by evaluating query predicates on the latest row version without requiring a lock.

Applicability for this new feature is limited to specific regions for now and is activated by default in both new and existing databases in these regions. Azure SQL users can verify the activation through a simple T-SQL query - SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('YOURDATABASENAME', 'IsOptimizedLockingOn').

Optimized Locking, however, relies on two other database features - Accelerated Database Recovery (ADR) and Read Committed Snapshot Isolation (RCSI). Currently, both are enabled by default in Azure SQL Database, and users can verify through another T-SQL query.

The change in locking behavior significantly reduces the number of locks, enhancing the system’s performance and efficiency. The Optimized Locking feature, which needs a support ticket for disabling, is not available for SQL Server on-premise at the moment.

So, what about Business Management Solution? Currently, D365 Business Central employs a repeatable read isolation level, where a transaction only sees data committed before it starts. It shares an interesting interplay with the new Azure SQL locking feature. While the current default isolation level doesn’t offer immediate advantages in our favorite all-in-one business management solution, the scenario is set to change.

The 2023 wave 1 release of D365 Business Central will allow developers to set transaction isolation levels via AL code, a much-anticipated addition to ramp up performance and lock management. Besides, the default isolation level in AL will soon change to “read committed”, making all these Azure SQL improvements available from the backend. Indeed, that will make a massive difference in locking and, by extension, system performance.

Moving forward, developers need to embrace this shift by acquiring a deeper understanding of Azure SQL and the intricacies of Optimized Locking and its impact on D365 Business Central. Microsoft offers a wide range of online training courses and certifications tailored for IT professionals, developers, and data engineers. "Microsoft Certified: Azure Database Administrator Associate" and "Microsoft Certified: Azure Developer Associate" are two recommended training programs that will provide deep insights into these areas.

In conclusion, the Optimized Locking feature stands in testament to Microsoft’s commitment to infusing efficiency and productivity in businesses. Along with the future improvements in D365 Business Central, businesses can expect more effective and seamless operations.

Keywords

Dynamics 365 Business Central SEO, Azure SQL optimization, Optimized Locking Azure, Business Central Azure SQL, Dynamics 365 Optimized Locking, Azure SQL Business Central, Dynamics 365 Azure SQL, Business Central Optimized Locking, Azure SQL Dynamics 365, Optimized Locking Business Central