Dynamics 365 Business Central, Azure SQL and Optimized Locking

Optimized locking is a new Azure SQL database engine feature recently announced that drastically reduces lock memory and the number of locks concurrently required for writes. Optimized locking helps on reducing lock memory as very few locks are held for large transactions. In addition, optimized locking also avoids lock escalations and this allows more concurrent access to the same table.

Optimized locking uses two primary components: Transaction ID (TID) locking (also used in other row versioning features) and lock after qualification (LAQ). It does not require any additional configuration. For more details:

• A transaction ID (TID) is a unique identifier of a transaction. Each row is labeled with the last TID that modified it. Instead of potentially many key or row identifier locks, a single lock on the TID is used.
• Lock after qualification (LAQ) is an optimization that evaluates predicates of a query on the latest committed version of the row without acquiring a lock, thus improving concurrency. 

The new Optimized Locking feature is currently available only in the following regions:

• West Europe
• UK South
• Canada Central
• Brazil South
• West Central US

and in these regions, optimized locking is activated by default in both new and existing databases.

You can verify if it’s enabled in your Azure SQL database by using the following query:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('YOURDATABASENAME', 'IsOptimizedLockingOn');

Optimized locking relies on other two database features:

Both accelerated database recovery and read committed snapshot isolation are now enabled by default in Azure SQL Database. To verify that these options are enabled for your current database, use the following T-SQL query:

SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM  sys.databases
WHERE name = db_name();

With this new locking behavior, a lock is taken on the transaction ID (TID) of the row instead of applying the lock on the key of the row. With TID locking, page and row locks continue to be taken for updates, but each page and row lock is released as soon as each row is updated. The only lock held until end of transaction is the exclusive lock on the TID resource, replacing page and row (key).

If you consider a table and perform 3 insert and 1 update, instead of having 4 locks now you have only a single lock:

CREATE TABLE TESTLOCKS
(a int PRIMARY KEY not null
,b int null);
INSERT INTO TESTLOCKS VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRAN
UPDATE TESTLOCKS
SET b=b+10;
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');
COMMIT TRAN
GO

This new feature will help on reducing locks on Azure SQL databases and cannot be disabled (you need a support ticket for that). Optimized locking is also currently not available for SQL Server on-premise.

What about Dynamics 365 Business Central?

Actually, Dynamics 365 Business Central uses repeatable read isolation level. The Repeatable Read isolation level only sees data committed before the transaction starts, it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. Just to give an example, imagine to execute the following SQL code:

BEGIN TRANSACTION;
SELECT * FROM MYTABLE;
WAITFOR DELAY '00:01:00'
SELECT * FROM MYTABLE;
COMMIT;

This SQL code executes two reads from MYTABLE, with a delay of 1 minute between them.

If the database uses REPEATABLE READ the second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added or changed by a concurrent transaction in the 1 minute delay period, but they will not seen from the second select. If you are selecting the same row twice in a transaction, you will get the same results both the times. If someone is modifying the data, you cannot even read those data until they complete the update.

You can easily check this by connecting to SQL Management Studio,and opening two different session (query window).

In the first session execute the following query by using the previously created TESTLOCKS Table:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
 
BEGIN TRAN  
 
SELECT *FROM TESTLOCKS
WAITFOR DELAY '00:01:00'
SELECT *FROM TESTLOCKS
 
ROLLBACK  

and during the execution of this query, in the second query window execute an update operation:

UPDATE TESTLOCKS set b = 5 where a = 1;

You will see that you will not be able to immediately update the data but the data is updated only whe the first transaction is committed:

If the database uses READ COMMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.

To test that, simply repeat the above example by changing the database transaction isolation level to READ COMMITTED in the previously opened first query window:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
BEGIN TRAN  
 
SELECT *FROM TESTLOCKS
WAITFOR DELAY '00:01:00'
SELECT *FROM TESTLOCKS
 
ROLLBACK  

You will see that now the update from the second session is immediately performed also if the query of the first session is in progress:

Due to this default isolation level, you will not see immediatly advantages in Dynamics 365 Business Central with this new Azure SQL locking feature. but things will change in the near future…

In Dynamics 365 Business Central 2023 wave 1 (v22) developers will be able to set transaction isolation level via AL code and this will be a great addition to tune up performances and locks.

By default, the Dynamics 365 Business Central runtime automatically determines the isolation levels used when querying the database. In 2023 wave 1 release a new ReadIsolation method will be introduced on the record data type (and it can also be invoked using property access syntax):

rec.ReadIsolation := IsolationLevel::

The possible isolation levels values that you will be able to explicitly set are the following:

But the great hidden news will be that soon the default isolation level in AL will be read committed. This will have a huge impact on locking because all these new Azure SQL improvements will be available from the backend.

I’m personally very happy for these Azure SQL improvements. I saw better locking handling on different cloud-based applications I have and I’m sure that also Dynamics 365 Business Central will have huge benefits.

This post was originally published on this site