Locking Scope: Differences between LockTable and ReadIsolation

Today, once again, I am returning to the topic of table locking, lock escalation, and the way Business Central interacts with SQL Server when acquiring locks.

I could not attend the BC Tech Days event this year, but even those who were not at the venue, could see practically live translation on Twitter, LinkedIn, or any social network of their preference. While I was watching this live stream of photos and comments, one particular shot caught my attention. One of the sessions in the Tech Days was dedicated to table locking: BC TechDays 2023 - Locking in AL: Runtime and explicit AL control. The key take-aways of the session included a recommendation to use the ReadIsolation property and set it to IsolationLevel::UpdLock to control table locks as opposed to the LockTable function. This clear preference sounded as a surprise to me, because from the database perspective, the effect of both IsolationLevel::UpdLock and LockTable looks the same. So I started my deep dive into the implementation of both methods of locking. But first of all, I need to give the necessary introduction and disband one common misconception about the LockTable method.

Introduction: What does LockTable actually do?

The most common answer that I hear when I ask this question is "It locks the table". Right? Well, it sounds very reasonable, but in fact, from the database point of view, LockTable does absolutely nothing. This method actually only changes the state of an internal object which indicates that the next SELECT statement will be issued with the UPDLOCK hint. The effect of the LockTable will be revealed only with the next AL data access function - Find() / FindSet() / FindFirst() / FindLast() / Get(). And only this Find* or Get that follows the LockTable will actually acquire the lock.

For example, the following AL code is translated to a SQL query that includes WITH(UPDLOCK) hint, and this hint instructs SQL Server to place the lock on the selected rows.

local procedure FindSalesHeadersWithLock()
var
    Customer: Record Customer;
begin
    Customer.LockTable();
    Customer.SetLoadFields(Name, Address);
    Customer.FindSet();
end;

And here is the resulting query. Note that this query is issued when the FindSet function is executed. LockTable does not trigger any activity on the SQL server.

SELECT
    "18"."timestamp",
    "18"."No_",
    "18"."Name",
    "18"."Address",
    "18"."Shipping Agent Code",
    "18"."Country_Region Code",
    "18"."$systemId",
    "18"."$systemCreatedAt",
    "18"."$systemCreatedBy",
    "18"."$systemModifiedAt",
    "18"."$systemModifiedBy"
FROM "CRONUS".dbo."CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" "18"
WITH(UPDLOCK) ORDER BY "No_" ASC OPTION(FAST 50)

This is all of the short introduction I wanted to make: LockTable does not lock anything, but instructs the next data read function to acquire the lock on the selected records.

Example: Locking on different variable instances

But back to the question that opened the post - why is IsolationLevel::UpdLock is preferred to the LockTable? At first glance, there is no difference in the behaviour of these two. If I now change my AL procedure and assign the isolation level to the record variable instead of calling LockTable, the resulting SQL query will be absolutely the same.

local procedure FindSalesHeadersWithLock()
var
    Customer: Record Customer;
begin
    Customer.ReadIsolation := IsolationLevel::UpdLock;
    Customer.SetLoadFields(Name, Address);
    Customer.FindSet();
end;

When this procedure is executed, the query sent to SQL Server will the same as the previous example despite the difference in the AL code.

To demonstrate the difference between ReadIsolation and LockTable, I need to build a slightly more complicated example. This example begins from a page action trigger which invokes LockTable on a local instance of the SalesHeader table. It is important to note that the trigger does not read any records, therefore it does not actually lock the table.

trigger OnAction()
var
    SalesHeader: Record "Sales Header";
    SessionId: Integer;
begin
    SalesHeader.LockTable();
    
    OnRunLockTableTestAfterSetRange();

    StartSession(SessionId, Codeunit::"Locking Demo Background Task");

    Sleep(11000);
end;

After setting the lock state on the record variable, this trigger calls an event publisher which is declared below. StartSession is called here to simulate another user session, just to demonstrate the locking behaviour. Sleep also facilitates the test to give the background session sufficient time to start and attempt to run its own update.

[IntegrationEvent(false, false)]
local procedure OnRunLockTableTestAfterSetRange()
begin
end;

This is the event publisher triggered from the page action above.

When we publish an event and release owr objects into the wild, we never know what kind of code is going to be executed in the subscriber and how it is going to impact the publisher.

Let's assume that our subscriber declares its own instance of the Sales Header record and selects all orders - with the default isolation, not attempting to set any locks.

codeunit 50102 "Locking Demo Subscriber"
{
    [EventSubscriber(ObjectType::Page, Page::"Locking Demo",
         'OnRunLockTableTestAfterSetRange', '', true, true)]
    local procedure FindSalesOrdersOnRunLockTableTest()
    var
        SalesHeader: Record "Sales Header";
    begin
        SalesHeader.SetRange(
            "Document Type", SalesHeader."Document Type"::Order);
        SalesHeader.FindSet();
    end;
}

And finally, the background session imitates activity of another user who is trying to update a certain sales order. Sales order 101005 is one of the demo dataset and is arranged to be shipped to Manchester. But now someone decided that this was a mistake, and the order should be shipped to Leeds instead.

codeunit 50101 "Locking Demo Background Task"
{
    trigger OnRun()
    var
        SalesHeader: Record "Sales Header";
    begin
        SalesHeader.Get(SalesHeader."Document Type"::Order, '101005');
        SalesHeader."Sell-to City" := 'Leeds';
        SalesHeader.Modify(true);
    end;
}

Don't be like me - don't assign the city on the sales header without validation. I do it now just for the sake of the demo.

Let's deploy the code now and run the test.

And what do we see when the action is executed? A timeout exception from the background session which is trying to update the sales order.

Timeout exception is raised because the background session cannot modify the Sales Header table which is locked by the main session. But how come the Sales Header gets locked? The event subscriber - the only piece of code that actually reads the data from the table, does not request the lock. And the action trigger which calls LockTable does not read any records. And the two don't share any variable instances. So what is locking it?

Let me keep the suspense for a little longer. Before I explain the locking in this example, I want to change it slightly and replace LockTable with ReadIsolation.

So now the sample AL action trigger will look like this:

trigger OnAction()
var
    SalesHeader: Record "Sales Header";
    SessionId: Integer;
begin
    SalesHeader.ReadIsolation := IsolationLevel::UpdLock;
    
    OnRunLockTableTestAfterSetRange();

    StartSession(SessionId, Codeunit::"Locking Demo Background Task");

    Sleep(11000);
end;

When this new version of the trigger is executed, the second session successfully updates the "Sell-to City" field in the order - it goes to Leeds now.

In my introduction of the LockTable method, I mentioned that this function does not leave any trace on the database level, but rather changes the state of an internal object in the Business Central server. Same applies to the ReadIsolation property. The important difference, though, is that the two methods change different states.

LockTable adds the table to the list of locked tables in the global SessionState object, thus affecting all instances of this record within the session. Once LockTable is called on any instance of a Record variable, all SELECT queries to the same table within the session will be augmented with the UPDLOCK hint until the end of the transaction.

ReadIsolation modifies the TableState object linked to the specific Record variable. This way, ReadIsolation affects only data access statements related to the same variable. Any other data access requests are executed with their own isolation level.

Another example of locking granularity

If the first code example looks a bit contrived, you are probably not far from the truth. After all, we rarely (if ever) place LockTable far from a Find or Get. But this example was intended to demonstrate the effect of the LockTable on all variable instances within the session - the actual locking does not have to happen, LockTable itself changes the session state, forcing all subsequent data access statements to use locking.

Here is a slight modification of the same example which may look a little more practical.

The procedure that initiates the locking gets a sales order with the LockTable (probably with the intention to update it).

local procedure GetSalesOrderWithLock()
var
    SalesHeader: Record "Sales Header";
begin
    SalesHeader.LockTable();
    SalesHeader.Get(SalesHeader."Document Type"::Order, '101005');

    OnAfterGetSalesOrderWithLock();
end;

Same procedure publishes an event which fires after the locking happens.

[IntegrationEvent(false, false)]
local procedure OnAfterGetSalesOrderWithLock()
begin
end;

An event subscriber from another application needs to retrieve sales invoices with no update intent - just calling FindSet on a local instance of the Sales Header.

[EventSubscriber(ObjectType::Page, Page::"Locking Demo",
    'OnAfterGetSalesOrderWithLock', '', true, true)]
local procedure FindInvoicesOnAfterGetSalesOrderWithLock()
var
    SalesHeader: Record "Sales Header";
begin
    SalesHeader.SetRange(
        "Document Type", SalesHeader."Document Type"::Invoice);
    SalesHeader.FindSet();
end;

Now let's see what happens in the database when this code is executed.

The first SELECT retrieves a single sales order (WHERE clause includes conditions on both Document Type and No.) This is a locking statement because of the LockTable in the AL procedure.

SELECT
    // Skipping the long list of fields
FROM "CRONUS".dbo."CRONUS International Ltd_$Sales Header$437dbf0e-84ff-417a-965d-ed2bb9650972" "36"
WITH(UPDLOCK)
WHERE ("36"."Document Type"=@0 AND "36"."No_"=@1) OPTION(FAST 50)

And this is the statement triggered by the event subscriber - it selects all sales invoices.

SELECT
    // Skipping the long list of fields
FROM "CRONUS".dbo."CRONUS International Ltd_$Sales Header$437dbf0e-84ff-417a-965d-ed2bb9650972" "36"
WITH(UPDLOCK)
WHERE ("36"."Document Type"=@0)
ORDER BY "Document Type" ASC,"No_" ASC OPTION(FAST 50)

But wait. What just happened? The event subscriber locked all invoices without any intention of doing so - because of the locking state initiated by the publisher. So a hypothetical second user session that would try to update any invoice at this moment, would be locked and possibly fail.

This is the locking granularity issue which ReadIsolation successfully solves. If I replace LockTable with IsolationLevel::UpdLock to indicate my update intent, this will limit the scope of the lock to the single record selected in the event publisher and sales invoices will remain unaffected.

SQL Lock Escalation

One last thing before I finish my long (but I hope not too boring) excursion into the locking scope is one slide from the Tech Days presentation. The slide shows a hierarchical diagram of locking escalation from the row level to page level and

finally to the whole table.

Green arrow and red lines crossing out the page level are my additions.

This is another common misconception around locking. If we refer to the official SQL Server documentation, we can find a clear statement

Lock escalation always escalates to a table lock, and never to a page lock

This is a misconception rooted in the fact that SQL Server does indeed place a page lock on pages that contain locked rows. But this is so called "intent lock". When an update (U) lock is acquired on a row, an intent update (IU) is placed on the page in which this row is located. But this is not a part of the escalation process - this is a normal locking behaviour. Page-level intent lock is acquired even if just a single row is locked.

Lock is always escalated from row level to the HoBT level (yes, this is pronounced "hobbit lock"). Without delving too much into details, we can say that in case of BC tables, it is always escalation from rows to the table level, since BC does not have any heap tables.

Besides this, intent update locks are compatible - IU lock will not prevent another process from placing an update lock and modifying the record. Some more details on this topic in one of my previous posts "Introduction to lock escalation".