Recently I was debugging a very interesting deadlock case which I want to share in a blog post now. It was especially curious because, as it turned out, it was caused by the Business Central's bulk inserts feature, and it took me a while to reproduce the deadlock, with bulk inserts usually disabled on my dev instance.
But one thing at a time. First of all, the scenario that led to the deadlock situation is:
Two (at least two, maybe more) concurrent sessions insert records in two tables;
One of these tables is relatively small, and only one record is inserted at a time (we can call it a header table);
The second table may be much larger than the first, or multiple records are inserted in a single transaction (a typical document line table);
Both tables have SIFT indexes defined on them, so every insert updates an associated SIFT view.
I will show a few code snippets to illustrate the process, and the full example can be found on GitHub.
For the sake of an example, let's consider two tables with the following structure. These are a document header and a document line tables, but the header has an item reference with some quantity (a structure similar to a production order or and assembly order).
table 50180 "My Document Header"
{
DataClassification = CustomerContent;
fields
{
field(1; "No."; Code[20]) { }
field(2; Description; Text[50]) { }
field(3; "Item No."; Code[20]) { }
field(4; Quantity; Decimal) { }
}
keys
{
key(PK; "No.")
{
Clustered = true;
}
key(Qty; "Item No.")
{
SumIndexFields = Quantity;
}
}
}And the header has an index on the Item No. with a SIFT on the Quantity field.
Similarly, the line table contains an Item No. field (it does not have to be the same item as the one in the header, though, we can think of these items as an assembled item and a list of components). What is important here is that this table also defines a SIFT index which must be updated on every insert.
And the record in this table is slightly larger too. I added ten dummy fields just for the buffered inserts to be triggered faster. The number of fields in the query is the key criterion for the decision to flush the record buffer, so it is a little more convenient to have a larger record in this demo.
table 50181 "My Document Line"
{
DataClassification = CustomerContent;
fields
{
field(1; "Document No."; Code[20]) { }
field(2; "Line No."; Integer) { }
field(3; Description; Text[50]) { }
field(4; "Description 2"; Text[50]) { }
field(5; "Item No."; Code[20]) { }
field(6; Quantity; Decimal) { }
field(7; "Dimension 1 Value"; Code[20]) { }
field(8; "Dimension 2 Value"; Code[20]) { }
field(9; "Dimension 3 Value"; Code[20]) { }
field(10; "Dimension 4 Value"; Code[20]) { }
field(11; "Some Dummy Field 1"; Code[10]) { }
field(12; "Some Dummy Field 2"; Code[10]) { }
field(13; "Some Dummy Field 3"; Code[10]) { }
field(14; "Some Dummy Field 4"; Code[10]) { }
field(15; "Some Dummy Field 5"; Code[10]) { }
field(16; "Some Dummy Field 6"; Code[10]) { }
field(17; "Some Dummy Field 7"; Code[10]) { }
field(18; "Some Dummy Field 8"; Code[10]) { }
field(19; "Some Dummy Field 9"; Code[10]) { }
field(20; "Some Dummy Field 10"; Code[10]) { }
}
keys
{
key(PK; "Document No.", "Line No.")
{
Clustered = true;
}
key(Qty; "Item No.", "Dimension 1 Value", "Dimension 2 Value")
{
SumIndexFields = Quantity;
}
}
}
And then I run two sessions inserting two documents. The first document is small and contains only one line.
codeunit 50180 "Insert Document 1"
{
trigger OnRun()
var
MyDocumentCreate: Codeunit "My Document - Create";
begin
MyDocumentCreate.InsertHeader('DOC1', 'ITEM1');
MyDocumentCreate.InsertLine(
'DOC1', 10000, 'ITEM2', 'Dim1Value', 'Dim2Value');
Sleep(500);
end;
}Whilst the second document is slightly bigger, and consists of five lines.
codeunit 50182 "Insert Document 2"
{
trigger OnRun()
var
MyDocumentCreate: Codeunit "My Document - Create";
begin
MyDocumentCreate.InsertHeader('DOC2', 'ITEM1');
MyDocumentCreate.InsertLine(
'DOC2', 10000, 'ITEM2', 'Dim1Value', 'Dim2Value');
MyDocumentCreate.InsertLine(
'DOC2', 20000, 'ITEM3', 'Dim1Value', 'Dim2Value');
MyDocumentCreate.InsertLine(
'DOC2', 30000, 'ITEM4', 'Dim1Value', 'Dim2Value');
MyDocumentCreate.InsertLine(
'DOC2', 40000, 'ITEM5', 'Dim1Value', 'Dim2Value');
MyDocumentCreate.InsertLine(
'DOC2', 50000, 'ITEM6', 'Dim1Value', 'Dim2Value');
Sleep(1000);
end;
}Note that the item in the header in both documents is the same, so both sessions will be updating the same SIFT key in the indexed view linked to the header table. And the same applies to the lines. "DOC1" includes ITEM2 as a component in the line record, and the same item is present among the components in the second session. Therefore, both sessions will be sequentially updating the same SIFT keys. Although, as I showed earlier in one of my posts, since SIFT updates acquire range locks on indexed views, it is sufficient for these two sessions to update adjacent keys to trigger an update conflict.
Now I run the test with my typical dev configuration (buffered inserts disabled) - and both sessions run without much tension.
Highlighted lines in the SQL Profiler screenshot are the inserts into the My Document Header and My Document Line from two sessions. We can see that the first session (SPID 52) inserts its two records, while the other session (SPID 54) has to wait for 500 ms until the first one completes the transaction, and then the session with ID 54 does its own inserts - header first, then all five lines.
Now what happens if I change the buffered insert key to its "normal" enabled state?
Set-NAVServerConfiguration
-ServerInstance BC
-KeyName BufferedInsertEnabled
-KeyValue $trueI update the key value, restart the service, and run the test again. To see this result:
The same two processes, doing the same tasks are now deadlocked.
Why does this happen?
When we talk about deadlocks and deadlock prevention, one of the most basic and the most important considerations is the update sequence. Whenever multiple processes update the same tables, they must do it in the same sequence: table A, followed by table B, with table C afterwards, and so on. If one renegade process breaks the sequence and updates table C before A, this is a sure way to have a deadlock.
When the buffered inserts are disabled in my test, both sessions insert header records before lines (as the first screenshot illustrates), and sequentially update the related indexed views. But with buffered inserts, this sequence may not be observed, and this is what happens in the above example. The first session inserts a small batch of data - one header record with just one line, and the inserts happen right before committing the transaction. But the second session writes more data. NST record buffer can accumulate four of "My Document Line" records, but the fifth one triggers the insert, flushing the buffer, while the header has not been inserted yet. And this breaks the sequence: session 1 inserts the header, then the line; but session 2 inserts the lines first, followed by the header. And if both sessions must update the same SIFT keys, deadlock occurs.
How to avoid it? My solution was to eliminate the root cause, that is the inconsistent insert sequence. To make sure that the header is always inserted before the lines, I added a small change in my code. Instead of an unconditional call of Insert on the header table:
MyDocumentHeader.Insert();I check the insert's return value:
OK := MyDocumentHeader.Insert();Generally, I am the one advising against this approach to inserts. A line like
if MyDocumentHeader.Insert() then;conceals a possible insert error which can lead to inconsistencies, and it blocks the buffered inserts, impacting the performance of inserts. But in this case, blocking the buffered inserts was exactly my purpose. I want to make sure that the header record is always inserted before the lines, and the condition on the return value of the Insert will do the trick. When the return value is used, records are not accumulated in the buffer, but rather inserted immediately. And performance was not a concern in my case, since I know that this process always inserts exactly one header record per transaction.
This solution may not fit everyone and certainly isn't something I would recommend to use in every situation, but in one particular case it appeared to be helpful.