NST record cache bypassed for all isolation levels above ReadUncommitted, even within a locked transaction

[arsag]

1. Describe the bug
When Record.ReadIsolation is set to IsolationLevel::ReadCommitted, IsolationLevel::RepeatableRead, or IsolationLevel::UpdLock, the NST record cache is completely bypassed on every Get() call, causing a SQL Server round-trip on each invocation. This happens even within the same transaction where no data change has occurred, and where stronger isolation levels (such as RepeatableRead or UpdLock) would logically guarantee that the cached value cannot have changed.

By contrast, IsolationLevel::Default and IsolationLevel::ReadUncommitted correctly utilize the NST cache — the first Get() issues one SQL query and all subsequent calls are served from cache.

The performance difference is dramatic: 10,000 Get() calls on Company Information take ~20 ms with Default/ReadUncommitted, but ~2,000 ms with ReadCommitted, RepeatableRead, or UpdLock.

2. To Reproduce
Steps to reproduce the behavior:
Run the following test codeunit and observe the Duration column in the AL Test Tool results.

codeunit 50000 "Read Isolation Get Test"
{
    Subtype = Test;
    TestPermissions = Disabled;

    [Test]
    procedure GetCompanyInfo_Default()
    var
        CompanyInformation: Record "Company Information";
        i: Integer;
    begin
        // ~20 ms — NST cache is used after first Get()
        for i := 1 to 10000 do
            CompanyInformation.Get();
    end;

    [Test]
    procedure GetCompanyInfo_ReadUncommitted()
    var
        CompanyInformation: Record "Company Information";
        i: Integer;
    begin
        // ~20 ms — NST cache is used after first Get()
        CompanyInformation.ReadIsolation := IsolationLevel::ReadUncommitted;
        for i := 1 to 10000 do
            CompanyInformation.Get();
    end;

    [Test]
    procedure GetCompanyInfo_ReadCommitted()
    var
        CompanyInformation: Record "Company Information";
        i: Integer;
    begin
        // ~2000 ms — every Get() hits SQL Server
        CompanyInformation.ReadIsolation := IsolationLevel::ReadCommitted;
        for i := 1 to 10000 do
            CompanyInformation.Get();
    end;

    [Test]
    procedure GetCompanyInfo_RepeatableRead()
    var
        CompanyInformation: Record "Company Information";
        i: Integer;
    begin
        // ~2000 ms — every Get() hits SQL Server
        CompanyInformation.ReadIsolation := IsolationLevel::RepeatableRead;
        for i := 1 to 10000 do
            CompanyInformation.Get();
    end;

    [Test]
    procedure GetCompanyInfo_UpdLock()
    var
        CompanyInformation: Record "Company Information";
        i: Integer;
    begin
        // ~2000 ms — every Get() hits SQL Server
        CompanyInformation.ReadIsolation := IsolationLevel::UpdLock;
        for i := 1 to 10000 do
            CompanyInformation.Get();
    end;
}

3. Expected behavior
For IsolationLevel::RepeatableRead and IsolationLevel::UpdLock, once the first Get() has acquired the appropriate SQL Server lock on the row, subsequent Get() calls within the same transaction should be served from the NST record cache. The lock held by SQL Server already guarantees the row cannot be modified by another transaction, so re-reading from SQL provides no additional consistency benefit — it only adds unnecessary network and I/O overhead.

For IsolationLevel::ReadCommitted, reusing the cache is a more nuanced question, but the current ~100x performance regression compared to Default suggests no cache optimization is applied at all.

4. Actual behavior
Any ReadIsolation level above ReadUncommitted causes every Get() to issue a SQL query to SQL Server, regardless of whether the record is already in cache or whether the transaction holds locks that guarantee row stability. The behavior is effectively identical to calling SelectLatestVersion() before every Get().

Observed durations for 10,000 Get() calls on Company Information:
Isolation Level | Duration
Default | 24 ms
ReadUncommitted | 20 ms
ReadCommitted | 2,153 ms
RepeatableRead | 1,866 ms
UpdLock | 1,893 ms
SelectLatestVersion() per call | 2,177 ms

5. Versions:

• AL Language: 17.0
• Business Central: 28

Final Checklist