Record instance isolation level

This article explains how you can use the ReadIsolation method to control isolation level on database transactions. The isolation level on a transaction determines the degree to which it's isolated from other transactions to prevent problems in concurrent situations. On the record level, the isolation level improves the integrity and stability of data when multiple transactions are reading the same record. It protects a transaction from the effects of other transactions by taking locks, preventing reads of uncommitted data, or preventing modifications.

Database locking can be a major cause for performance issues. When AL code takes fewer locks, it increases the performance of the system for end users. By using record instance isolation level, you can improve performance by limiting database locks to only what necessary.

How isolation level works by default

The runtime of Business Central automatically determines the isolation levels used when querying the database. A transaction's isolation level is heightened either implicitly by writes on a record or explicitly via a LockTable method call, both on a per-table basis. The heightened isolation level persists for the entirety of the transaction—leaving subsequent code executed be impacted by heightened isolation levels, whether it's required or wanted.

The below example shows AL code with SQL isolation level hints annotated on database reads, which solely relies on transaction determined locking.

local procedure CurrentBehavior()
var
    cust: Record Customer;
    otherCust: Record Customer;
    curr: Record Currency;
begin
    cust.FindFirst(); // READUNCOMMITTED

    // Heighten isolation level for Customer table.
    cust.LockTable();

    cust.FindLast(); // UPDLOCK

    // Also impacts other instances of same table.
    otherCust.FindSet(); // UPDLOCK

    // But does not impact other tables.
    curr.Find(); // READUNCOMMITTED
end;

Using record instance isolation level

With the introduction of record instance isolation level, it's possible to explicitly select the isolation level for reads on a record instance. Record instance isolation level overrides the transaction's isolation level for a given table. It's possible to both heighten and lower the isolation level, with the effect being localized to the record instance instead of lasting for the entire length of the transaction.

The following example shows AL code with SQL isolation level hints annotated on database reads, with record instance isolation level used to override the transaction's isolation level.

local procedure UsingReadIsolation()
var
    cust: Record Customer;
    otherCust: Record Customer;
    curr: Record Currency;
begin
    cust.FindFirst(); // READUNCOMMITTED

    // Heighten isolation level for Customer table.
    cust.LockTable();

    // Explicitly select another isolation level than the transaction's.
    otherCust.ReadIsolation := IsolationLevel::ReadUncommitted;

    otherCust.FindSet(); // READUNCOMMITED
end;

Isolation levels

The following table describes the different isolation levels of the IsolationLevel option type that you can apply:

Value	Description
Default	Follows the transaction's state. It's the same as not using read isolation.
ReadUncommitted	Allows dirty reads, which means it can read rows that have been modified by other transactions but not yet committed. It takes no locks and ignores locks from other transactions.
ReadCommitted	Allows reads on committed data only, in other words, it can't read data that has been modified by other transactions but not yet committed. But it doesn't guarantee that rows read will stay consistent throughout the entirety of the transaction.
RepeatableRead	Ensures all reads are stable by holding shared locks for the lifetime of the transaction. The transaction can't read data that has been modified but not yet committed by other transactions, and no other transactions can modify data that has been read by the current transaction until the current transaction completes.
UpdLock	Reads for update, disallowing others to read with the same intent.

For more about non-default values, go to SET TRANSACTION ISOLATION LEVEL and UPDLOCK in the SQL Server documentation.

Temporarily heightening the isolation level

Previously AL only provided explicit isolation level control via the LockTable method, which would ensure the all reads for the remainder of the transaction would use UpdLock. Instead, with record instance isolation level code can be explicit about the isolation guarantees it needs and leave subsequent code unimpacted by its execution.

The following example heightens the isolation level on a record instance of type "G/L Entry". It takes the lock on the last row, while subsequent reads won't trigger further locks to be taken. Such usage makes sense in cases with event subscribers, where one injects code into an existing business logic flow. Where it wasn't expected to introduce a LockTable call causing subsequent reads against a table to lock.

// Gets the next "Entry No." and locks just last row.
// Without causing the rest of transaction to begin taking locks.
local procedure GetNextEntryNo(): Integer
var
    GLEntry: Record "G/L Entry";
begin
    GLEntry.ReadIsolation := IsolationLevel::UpdLock;
    GLEntry.FindLast();
    exit(GLEntry."Entry No." + 1)
end;

Temporarily lowering the isolation level

It isn't possible inside a transaction to determine the current isolation level used in the transaction. If previously executed code has triggered a higher isolation level, counting on the entire table requires locks on the entire table. With record instance isolation level, for example, you could get an estimated record count without locking everyone else out from making changes to the table.

local procedure GetEstimatedCount(tableno: Integer) : Integer
var
    rref: RecordRef;
begin
    rref.Open(tableno);
    rref.ReadIsolation := IsolationLevel::ReadUncommitted;
    exit(rref.Count);
end;

Differences between transaction locking and record instance isolation level

When using FlowFields and the default transaction state, it's the state of the target table of the FlowField's formula that's used to determine the isolation level, not source table's target state. When using record instance isolation level, the target table doesn't matter, because the isolation level specified on the ReadIsolation method is used. Consider the following example.

local procedure Foo()
var
    purchLine: Record "Purchase Line";
    curr: Record Currency;
begin
    curr.FindFirst();

    curr.CalcFields(curr."Vendor Outstanding Orders"); // READUNCOMMITED

    purchLine.LockTable();

    curr.CalcFields(curr."Vendor Outstanding Orders"); // UPDLOCK

    curr.ReadIsolation := IsolationLevel::ReadUncommitted;

    curr.CalcFields(curr."Vendor Outstanding Orders"); // READUNCOMMITTED
end;

See also

LockTable