The dark side of SumIndexFields. Concurrency.

My previous post was dedicated to an overview of Business Central's SumIndexField Technology, or simply SIFT, which helps us collect aggregated data extremely fast by creating an indexed view, maintained by data update operations. This shift of the heavy lifting associated with data aggregation from reads to updates makes an impression of a magic tool that makes developers' life so much easier - just add a few more SIFTs to the table and the performance of reads skyrockets. At the same time, negative effects of SumIndexFields are often overlooked, primarily because updates of indexed views bite most painfully in highly concurrent environments. Concurrency is rarely a target of testing during development and UAT, and the problems may remain unnoticed until the the volume of data and the number of users scale up.

In this post, I am looking at the dark side of things - what we need to be aware of when adding a SumIndexFields property to a table key.

For all examples below, I will use the same table which was used previously to demonstrate positive effects of SIFT.

table 50501 "My Sales Table"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; "Entry No."; Integer)
        {
            Caption = 'Entry No.';
        }
        field(2; "Customer No."; Code[20])
        {
            Caption = 'Customer No.';
        }
        field(3; "Item No."; Code[20])
        {
            Caption = 'Item No.';
        }
        field(4; "Item Category Code"; Code[20])
        {
            Caption = 'Item Category Code';
        }
        field(5; "Customer Group Code"; Code[20])
        {
            Caption = 'Customer Group Code';
        }
        field(6; "Posting Date"; Date)
        {
            Caption = 'Posting Date';
        }
        field(7; Amount; Decimal)
        {
            Caption = 'Amount';
        }
    }

    keys
    {
        key(PK; "Entry No.")
        {
            Clustered = true;
        }
        key(CustomerItemDate; "Customer No.", "Item No.", "Posting Date")
        {
            SumIndexFields = Amount;
        }
        key(CustomerGroupNo; "Customer Group Code", "Customer No.")
        {
            SumIndexFields = Amount;
        }
        key(ItemCategoryNo; "Item Category Code", "Item No.")
        {
            SumIndexFields = Amount;
        }
    }
}

It is the same table which I used in the previous post introducing SIFT, but this time I added more keys with SumIndexFields. And this time, I also run multiple concurrent sessions inserting records into the table.

Ten sessions are triggered from a page:

trigger OnAction()
var
    I: Integer;
    SessionNo: Integer;
begin
    for I := 1 to 10 do
        StartSession(SessionNo, Codeunit::"Insert Fact Entry");
end;

And each one runs this code inserting records into My Sales Table.

trigger OnRun()
var
    MySalesTable: Record "My Sales Table";
    EntryNo: Integer;
begin
    for EntryNo := SessionId() * 100000 to
        (SessionId() + 1) * 100000 - 1 do
    begin
        MySalesTable."Entry No." := EntryNo;
        MySalesTable."Item No." := GetRandomDimValue(1);
        MySalesTable."Customer No." := GetRandomDimValue(2);
        MySalesTable."Item Category Code" := GetRandomDimValue(3);
        MySalesTable."Customer Group Code" := GetRandomDimValue(4);
        MySalesTable.Amount := Random(100000);
        MySalesTable."Posting Date" :=
            CalcDate('<-CY>', WorkDate()) + Random(365);
        MySalesTable.Insert();
    end;
end;

What is important to note in this code is that each session generates 100 000 entries whose Entry No. is based on the session ID, so it is guaranteed to be unique. At least during one test, assuming I delete all entries before starting this code. This is what I do - table is empty and I start the background sessions.

There are ten sessions, each inserting 100 000 entries, so I expect to find 1 000 000 records when the process is completed.

But instead, when I check the records count, contrary to my expectations, I see 100 000 records inserted by a single transaction. Nine sessions out of ten did not save the data.

Event log is full of lock timeout errors, confirming that only a single transaction survived.

We can't save your changes right now, because a record in table 'My Sales Table' is being updated in a transaction done by another session.

You'll have to wait until the other transaction has completed, which may take a while. Please try again later.

Now I delete the SIFTs - keeping the indexes (key declarations), just removing the SumIndexFields property - and run the same code again.

Once all sessions are completed, I check the record count in My Sales Table and find 1 000 000 records, just as expected. No lock timeout errors in the log this time, and all sessions successfully did all inserts.

This confirms that the indexed views introduced with SumIndexFields are the culprit here. But why? Let's dive deeper to see.

Updates of indexed views

Indexed views are not updated the same way as "normal" tables are. Inserting a table row places an exclusive lock on the key being inserted (plus intent lock on objects higher in the hierarchy). But inserts in indexed views acquire additional range locks held for the whole duration of the transaction. This behaviour of SQL Server lacks documentation, so main sources of information are blog posts by Erik Darling and Conor Cunningham, some Stack Exchange answers, and experimenting of course.

First, let's see what locks are taken when a row is inserted into a table without indexed views involved. I run an INSERT and use the following query to find active locks.

select
    locks.resource_type,
    locks.request_mode,
    locks.request_type,
    parts.object_id,
    parts.index_id,
    obj.name
from
    sys.dm_tran_locks locks
    join sys.partitions parts on 
        locks.resource_associated_entity_id = parts.partition_id
    join sys.objects obj on obj.object_id = parts.object_id

This is the query output.

One key in each of the five indexed is locked. These indexes are: one clustered index (or the table itself), 3 B-tree indexes explicitly declared in the table definition, and one index on the $systemId field which is always created on every table by the BC platform. Besides exclusive locks on index keys, SQL Server places intent exclusive locks on pages containing these keys.

If I restore the SumIndexFields property which I previously deleted and look at the locks acquired during the insert operation, I will see a different picture.

Obviously, there are more X and IX locks on keys and pages, because one key lock and one page lock must be taken on each of the SIFT views. But besides those, now we have these three extra lines:

KEY	RangeS-U	LOCK	1333083660	1	$VSIFT$ItemCategoryNo
KEY	RangeS-U	LOCK	1317083603	1	$VSIFT$CustomerGroupNo
KEY	RangeS-U	LOCK	1301083546	1	$VSIFT$CustomerItemDate

RangeS-U lock type locks a range between the key being inserted and the adjacent key, not allowing other transactions to insert any value between these two keys. If the new key is the last one in the index, RangeS-U locks the range from the current value to infinity, blocking all inserts after the last index key. This lock limits concurrency, effectively turning the transactions into serializable mode, since this is very similar to locking done in a serializable transaction. Unfortunately, information on range locks is scarce, but some details can be found in another blog post by Sunil Agarwal.

Example of insert conflict

To see what this means in practice, I will run a few more code snippets. For this example, I prepared two entries with customer numbers CUST0001 and CUST0010. It is important to keep track of the records ordering, so I've changed my random codes to something easier comprehensible. I also deleted 3 of the 4 SIFT views, keeping only the first one to avoid interference from locks on multiple indexed views. One active view now is "Customer No.", "Item No.", "Posting Date".

So, for now there are only two records in the table: one with customer No." = "CUST0001", the second one has the "Customer No." = "CUST0010", and "Item No." = "ITEM1" in both. Date is also the same in both, 31.12.2025, and other fields are irrelevant, since they are not included in the view.

Now I am inserting new records in two concurrent sessions.

Session 1:

trigger OnRun()
begin
    InsertMySalesEntry(
        1000010, 'CUST0005', 'ITEM2', '', '', 20251231D, 12345);
    Sleep(12000);
end;

Session 1 sleeps for a while to give the second session an opportunity for a lock timeout.

And Session 2 starts right after the start of Session 1.

trigger OnRun()
begin
    InsertMySalesEntry(
        1000020, 'CUST0020', 'ITEM2', '', '', 20251231D, 12345);
end;

When both are completed, I check the records in the table and the view. So far so good, all records are updated correctly, new entries have been inserted.

Then I delete two new records and rerun the same sessions, changing the number of the second customer from CUST0020 to CUST0009, so that it falls in the range between CUST0005 inserted by the first session, and the next existing key, which is CUST0010.

And now the insert in the second session fails due to a lock timeout. The reason of the timeout is the range lock held by Session 1 protecting the whole range of values between CUST0005 and CUST0020 from modifications.

Same (and even more dramatic) thing would happen if Session 1 was to insert an entry with the "Customer No." after CUST0020, which is the last value in the key range. In this case, no new values with the customer number greater than CUST0020 can be inserted, until Session 1 completes.

If I try to illustrate this process, this will be the picture for the first example. No conflicts - two sessions inserting values in different ranges.

In the second example, Session 2 attempts an insert in a range locked by Session 1 and fails with a lock timeout.

In the third and the las example, Session 1 is holding a lock on an infinite range, preventing all other transactions from inserting any values with Customer No. greater than CUST0020.

Deadlock example

This type of locking opens countless possibilities for deadlocks. Just consider two sessions inserting records as follows.

Session 1:

trigger OnRun()
begin
    InsertMySalesEntry(
        1000010, 'CUST0005', 'ITEM2', '', '', 20251231D, 12345);
    Sleep(2000);
    InsertMySalesEntry(
        1000020, 'CUST0020', 'ITEM2', '', '', 20251231D, 12345);
end;

Session 2:

trigger OnRun()
begin
    InsertMySalesEntry(
        1000030, 'CUST0040', 'ITEM2', '', '', 20251231D, 12345);
    Sleep(2000);
    InsertMySalesEntry(
        1000040, 'CUST0009', 'ITEM2', '', '', 20251231D, 12345);
end;

Nothing looks suspicious here - both sessions are inserting new records into the table in a way that can't cause any conflict in the table itself. Still the result of running this code is this:

This is a deadlock on the SIFT view caused by the range locks I just described. This example is very simple and be easily fixed by just ordering all inserts in both sessions in ascending order. But such ordering is not always possible in practice when we receive data from an external source. Adding another SIFT complicates things manifold, rendering any kind of ordering practically impossible. I've seen many deadlock graphs with three sessions entangled in a deadlock on two or three different SIFT keys.

The conclusion so far is that SumIndexField Technology in Business Central is a valuable tool to achieve better read performance, but there are hidden dragons. While performing so well on reads, indexed views backfire heavily on updates, often causing severe locking issues under high load.

Coming next: how SIFT keys affect performance of bulk loads without concurrency, and what alternatives do we have if SumIndexFields are so dangerous? Stay tuned.