Dynamics Community Forum Thread Details

Exceptions calling API under load - Deadlocked Sales Invoice Entity Aggregate table

We are creating a custom BC API that will be called by various external processes. The multi user nature means it can and will be called by multiple users within a short period of time.

During testing of service simulated perform a number of calls within short period of time (basic load testing). Under heavier loads we encounter numerous exceptions indication deadlocks.

The call in question creates an invoice. If it were posted immedialty it would via Sales-Post code unit it was easy to encounter exceptions - removing the post routine solved this issue but we need to be able to query customers balances.

So used the Post Job via Job Queue code unit to post a Sales Header via queue. This works great, but can still get deadlock exceptions under load. The thought was it would issues since invoices would be post one of a time out of the queue.

However can assume there is some activity during invoice/saleheader creation?

When the API call fails it returns a HTTP 409 (conflict) and returns the following message:

{"error":{"code":"Internal_ServerError","message":"The activity was deadlocked with another user who was modifying the Sales Invoice Entity Aggregate table. Please retry the activity. CorrelationId: xxxxxxxxxxxxxxxxxxxxx."}}

What is Sales Invoice Entity Aggregate primary purpose? What's the easiest way to search code units to find this? We assume that is used during invoice creation and posting and is locked during this period?

The following code contains the bulk of the logic for the Invoice Creation record creation but has been stripped of business specific logic. It should give

var

Card: Record Contact;

Cust: Record Customer;

SalesHeader: Record "Sales Header";

Invoice: Record "Sales Invoice Header";

SalesLine: Record "Sales Line";

Balance: Decimal;

SalesPostViaJobQueue: Codeunit "Sales Post via Job Queue";

begin

SalesHeader.Init();
SalesHeader.Validate("Sell-to Contact No.", contactNo);

SalesHeader."Reason Code" := 'acode';
SalesHeader.Validate("Document Date", Today());

SalesHeader."Document Type" := SalesHeader."Document Type"::Invoice;
SalesHeader."External Document No." := referenceNo;

SalesHeader.Insert(True);

SalesLine.Init();

SalesLine."Document Type" := SalesLine."Document Type"::Invoice;
SalesLine."Document No." := SalesHeader."No.";

//set GL account
SalesLine.Type := SalesLine.Type::"G/L Account";
SalesLine."Line No." := 10000;
SalesLine.Validate("No.", "6010");
SalesLine.Description := productcode;
SalesLine.Validate(Quantity, 1);
SalesLine.Validate("Unit Price", amount / 100);

SalesLine.Insert(True);

//calculate header totals
SalesHeader.CalcFields(Amount, "Amount Including VAT");

//post invoice - this was removed
//Codeunit.Run(Codeunit::"Sales-Post", SalesHeader);

//this partially solves problem but still get deadlocks.

//SalesPostViaJobQueue.EnqueueSalesDoc(SalesHeader);

Any help is appreciated

TIA.

Categories:

Dynamics 365 Business Central

Thanks for the reply.

I understand the locking issue - and I was hoping/assuming using the BC feature to post via queues as an option to alleviate this.

So even with the line the SalesPostViaJobQueue.EnqueueSalesDoc(SalesHeader); which successfully submits to queue we are observing locks.

I am assuming the BC queue would perform a post on one salesheader at a time - and if this were the case there shouldn't be any conflicts (assuming no other activity in system). Does BC process more than one job from queue at a time?

The option of having the external client queuing isn't viable since the application is time sensistive and requires a reasonably quick response.

Will probably schedule invoice posting after hours and change logic to include non-posted invoices when querying balances.

Quick Links

Leaderboard > Small and medium business | Business Central, NAV, RMS