New functionality: Control Database Isolation level on individual reads on a record instance

 

Hi,

We are familiar with the Record.LockTable property that we use to explicitly lock the table against write transactions that can conflict.

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

With Business Central v.22, a new ReadIsolation method has been introduced on the record data type. Its syntax is as:

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

IsolationLevel can have following values:

Default, ReadCommitted, ReadUncommitted, RepeatableRead and UpdLock

Example:

To understand the concept, we use two simple procedures. One procedure will get customer 10000 and modifies Name while other procedure gets the same customer 10000 and shows the Name. Add these functions to actions on any page, I added them on Customer card using Page Extension. We will call these functions concurrently and one after the other to cover different scenarios in different sessions. Sleep function is used to delay the commit.

Procedure 1:

procedure modifyOnly()

    var

        Customer: Record Customer;

    begin

        Customer.Get('10000');

        Customer.Name := 'Modified Name';

        Customer.Modify();

        Sleep(45000);

    end;

Procedure 2:

procedure Show()

    var

        Customer: Record Customer;

    begin

        Customer.ReadIsolation := IsolationLevel::RepeatableRead;

        Customer.Get('10000');

        Message(Customer.Name);

    end;

       

We will go through the Isolation Levels one by one.

Default: 

It follows the table isolation level for reads; same behavior as not setting Isolation Level.

Open Business Central and Run ModifyName that will put the system to Sleep for 45 seconds after Customer.Modify. So, record is modified but not yet committed.

Open another Business Central session in incognito window and Run ShowName procedure. You will see that ShowName procedure will show the modified name even though it is not yet committed to DB. It is an example of dirty read.

Result: If Default is used then system can read uncommitted data.

ReadCommitted: 

Allows to read only committed data.

Change IsolationLevel to ReadCommitted in code. Open Business Central and change the customer 10000 name back to Original Name. Run ModifyName procedure that will put the system to Sleep for 45 seconds after Customer.Modify. So, record is modified but not yet committed.

Open another Business Central session in incognito window and Run ShowName procedure. You will see that after a few seconds you will get error that We cannot save your changes…

Result: If ReadCommitted is used then Read operation can performed only on a fully committed record, if the record is in between a transaction error will be thrown as above.

ReadUncommitted: 

Allows the record to read data that has been modified by other transactions but not yet committed (also called dirty reads)

Its behaviour is same that we saw in Default. You can perform example in same way.

RepeatableRead: 

Reads only committed data (as we see in ReadCommitted) but it also locks the record until current transaction is completed, means no write transaction can be performed on that record.

To understand it a bit more make a little change to Procedure 2 and replace Message(Customer.Name) by Customer.Modify(); Change IsolationMethod to RepeatableRead.

Open Business Central and change customer name back to Original Name and refresh.

Run procedure#1 ModifyName that will modifies the name and sleep.

Open Business Central in another incognito window and run procedure 2. After a few seconds you will get the error that We cannot save your changes….

Result: RepeatableRead only reads committed data and put lock until transaction is completed.

UpdLock: 

Ensures that reads stay consistent for the life of the current transaction. Until transaction completes record cannot read uncommitted data and other transactions with same isolation level cannot read  data that was read by this record.

Same behaviour as RepeatableRead, but additionally it does not allow other transactions with same isolation level to read data that was read by it. So, it reads only committed data, lock the record for write transaction and block the record from reading for transactions with same isolation level.

For any feedback or if you find any mistakes/issues feel free to send email at raibilalhaider@yahoo.com