Preventing Database Deadlocks: Retry Patterns and Transaction Sequencing in AL

When multiple background tasks or concurrent user sessions attempt to modify overlapping sets of records in different orders, SQL Server encounters resource contention that can result in a database deadlock. When this occurs, the SQL engine terminates one of the sessions to resolve the conflict, throwing a runtime error in Business Central.

In this installment of The Perfomance Stack, we will look at why deadlocks happen, how updating records in a consistent order can reduce them, and how to retry safely when a deadlock still occurs.

The Problem

Consider an automated integration routine that processes inbound sales orders and updates associated inventory allocation records simultaneously across parallel job queue threads. Under heavy concurrent load, background tasks intermittently fail with errors indicating that a transaction was deadlocked on a resource and chosen as the deadlock victim. The affected job queue entry aborts, requiring manual intervention or scheduled retries that may fail again under the same concurrency conditions.

A deadlock occurs when two or more concurrent sessions hold exclusive locks on separate database resources while attempting to acquire locks on resources held by the other session. Neither session can proceed, forcing the SQL Server query optimizer to abort one transaction. In AL, this typically happens when different code paths modify parent and child tables (such as Sales Header and Sales Line or Item and Item Ledger Entry) in inconsistent order across concurrent threads.

 procedure ProcessBatchBaseline(var SalesHeader: Record "Sales Header")
    begin
        if SalesHeader.FindSet() then
            repeat
                // Modifying parent then child in arbitrary order across parallel threads
                SalesHeader.LockTable();
                SalesHeader.Status := SalesHeader.Status::Released;
                SalesHeader.Modify();

                UpdateSalesLines(SalesHeader."No.");
            until SalesHeader.Next() = 0;
    end;

    local procedure UpdateSalesLines(SalesOrderNo: Code[20])
    var
        SalesLine: Record "Sales Line";
    begin
        SalesLine.SetRange("Document No.", SalesOrderNo);
        if SalesLine.FindSet(true) then
            repeat
                SalesLine.Validate("Shipment Date", WorkDate());
                SalesLine.Modify();
            until SalesLine.Next() = 0;
    end;

A typical multi-document processing routine iterates through a list of records and modifies related parent and child tables without enforcing a strict, deterministic sequence.

When multiple worker threads process overlapping batches where Thread A locks Order 100 then tries to lock Order 101, while Thread B locks Order 101 then Order 100, SQL Server cannot reconcile the conflicting lock chains. A deadlock is triggered immediately.

To understand deadlock resolution, we must look at how the Business Central Server tier interacts with SQL Server transaction isolation levels.

SQL Server automatically detects deadlocks and selects a victim based on lowest resource consumption to rollback. Business Central catches this SQL exception and surfaces it to the AL runtime. Because transient deadlocks are environmental concurrency collisions rather than persistent data bugs, aborting the entire process without a retry mechanism degrades automated batch reliability.

Resource Impact

• Throughput: Collapses when concurrent background workers collide and abort, leaving work unfinished.
• Reliability: Introduces fragility into automated integration pipelines.
• Maintainability: Often mismanaged by simply wrapping entire processes in generic try functions that swallow legitimate execution errors.

The Fix

Mitigating deadlocks requires two complementary strategies:

• Deterministic record sorting – ensuring all sessions acquire locks in the exact same order
• Transient error retry handling – catching deadlock exceptions to safely re-execute the unit of work.

procedure ProcessBatchWithRetry(var SalesHeader: Record "Sales Header")
var
    RetryCounter: Integer;
    MaxRetries: Integer;
    Success: Boolean;
    LastErrorText: Text;
begin
    MaxRetries := 3;  
    // Ensure deterministic processing order to minimize lock collisions
    SalesHeader.SetCurrentKey("Document Type", "No.");
    
    if SalesHeader.FindSet() then
        repeat
            RetryCounter := 0;
            Success := false;
            
            while not Success and (RetryCounter < MaxRetries) do
                // Call the TryFunction procedure
                if ProcessSingleOrderTryFunction(SalesHeader."No.") then
                    Commit();
                    Success := true
                else begin
                    LastErrorText := GetLastErrorText();
                    RetryCounter += 1;
                    
                    if not IsDeadlockError(LastErrorText) or (RetryCounter >= MaxRetries) then
                        Error(LastErrorText);
                    
                    // Brief pause before retry backoff
                    Sleep(50 * RetryCounter);
            end;
        until SalesHeader.Next() = 0;
end;

[TryFunction]
local procedure ProcessSingleOrderTryFunction(OrderNo: Code[20])
begin
    ProcessSingleOrderTransactional(OrderNo);
end;

local procedure ProcessSingleOrderTransactional(OrderNo: Code[20])
var
    LocalSalesHeader: Record "Sales Header";
    LocalSalesLine: Record "Sales Line";
begin
    // Explicit transaction boundary per document
    LocalSalesHeader.Get(LocalSalesHeader."Document Type"::Order, OrderNo);
    LocalSalesHeader.LockTable();
    LocalSalesHeader.Status := LocalSalesHeader.Status::Released;
    LocalSalesHeader.Modify();

    LocalSalesLine.SetRange("Document No.", OrderNo);
    if LocalSalesLine.FindSet(true) then
        repeat
            LocalSalesLine.Validate("Shipment Date", WorkDate());
            LocalSalesLine.Modify();
        until LocalSalesLine.Next() = 0;
    
end;

local procedure IsDeadlockError(ErrorText: Text): Boolean
begin
    // Check against standard SQL deadlock error signatures surfaced by platform
    exit(ErrorText.Contains('deadlocked') or ErrorText.Contains('lock collision'));
end;

Why this Helps

• Deterministic Ordering: By enforcing SetCurrentKey on primary sorting fields, concurrent threads attempt to acquire locks in a predictable sequence, drastically reducing cross blocking.
• Targeted Retries: Confined try catch blocks isolate failures to individual documents and retry transient deadlocks without failing the entire multi-thousand record batch.

Retrying transactions consumes additional database cycles if conflicts are frequent. If your application experiences constant deadlocks, the root cause is poor architectural transaction scoping or missing database indexes, retries are a safety net, not a substitute for proper query design.

When NOT to use it

• Non-Transient Errors: Never retry errors caused by invalid master data setup, missing mandatory fields, or permission issues.
• Unbounded Loops: Avoid wrapping massive, uncommitted multi-hour batch runs in a single retry loop, keep transaction units granular (e.g., per document).

The Bottom Line

Minimize lock duration and standardize acquisition order. When multiple processes compete for shared database resources, agreeing on a predictable locking sequence and keeping transactions as short as possible eliminates circular blocking chains.