Business Central 2023 wave 1 (BC22) new features: Control database locking behavior (new ReadIsolation method)

Hi, Readers.
The public preview for Dynamics 365 Business Central 2023 release wave 1 (BC22) is available. Learn more: Link.

I will continue to test and share some new features that I hope will be helpful.

Control database locking behavior:

Business value:
Database locking is one of the main root causes for performance issues. When AL code takes fewer locks, it increases the performance of the system for users.
https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave1/smb/dynamics365-business-central/control-database-locking-behavior

Note: The public preview of the feature is in April 2023, and as of now (2023.03.17) only some of the features were available in the public preview version.

We have discussed Database Locks before. Database locking controls access by multiple users to the same data at the same time. To protect a transaction against other transactions modifying the same data, the first transaction puts a lock on the data. The lock remains until the transaction’s done. More details:

By default, the runtime of Business Central automatically determines the isolation levels used when querying the database. With this release, AL developers can now explicitly control the database isolation level on individual reads on a record instance.

A new ReadIsolation method has been introduced on the record data type. The method has the following syntax:

rec.ReadIsolation := IsolationLevel::<enum value>

For example,

The following table describes the possible IsolationLevel values:

Value	Description
Default	Follows the table’s isolation level for reads; same behavior as not setting an IsolationLevel.
ReadCommitted	Allows reads on committed data only; not data that’s been modified by other transactions but not yet committed.
ReadUncommitted	Allows the record to read data that has been modified by other transactions but not yet committed (also called dirty reads). A ReadUncommitted transaction takes no locks and ignores locks from other transactions.
RepeatableRead	Ensures that reads stay stable for the life of the current transaction. Until the current transaction completes, the record can’t read data that has been modified but not yet committed by other transactions and other transactions can’t modify data that has been read by the current transaction.
UpdLock	Ensures that reads stay consistent for the life of the current transaction. Until the current transaction completes, the record can’t read data that has been modified but not yet committed by other transactions and other transactions with the same isolation level can’t read data that was read by the record.

Default:

ReadCommitted:

ReadUncommitted:

RepeatableRead:

UpdLock:

We can set the database isolation level before processing the data.

At present, MS Learn (Docs) for the ReadIsolation method has not been updated. I look forward to seeing more information about this at the Business Central Launch Event (2023 release wave 1). Give it a try!!!😁

PS: This looks somewhat similar to SET TRANSACTION ISOLATION LEVEL in SQL Server.

Update info from Business Central Launch Event (2023 release wave 1)
Session: What’s new in VS Code and AL (It is recommended to check out the Business Central Launch Event directly to learn more about new features)

END

Hope this will help.

Thanks for reading.

ZHU