Hi,
AX Version : AX 2012 R3 CU 12
AX is integrating with 3rd party application where the sales order creation and posting is automated.
while trying load test(testing with multiple users) getting deadlock error :
Cannot delete a record in Sales order - update (SalesParmUpdate). Document status: Invoice, SI-016204.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.
Cannot edit a record in Sales order line - update table (SalesParmLine). Line number: 0,0000000000.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.
Below is my code to post sales invoice:
[
SysEntryPointAttribute(true),
AifCollectionTypeAttribute(“return”,Types::String, “Notes”)
]
public Notes PostSalesInvoice(SalesId _subJobNo, InvoiceId _invoiceNo)
{
AxSalesParmTable axSalesParmTable;
AxSalesParmLine axSalesParmLine;
SalesParmTable salesParmTable;
SalesParmLine salesParmLine;
SalesTable salesTable;
SalesLine _salesLine;
SalesLine salesLine;
WLKSalesTableStageTable wlkSalesStageTable;
WLKSalesLineStageTable wlkSalesLineStageTable;
CreatedTransactionId createdTransactionId;
WLKSalesLineStageService service;
WLKSalesTableStageTable upWlkSalesTabStg;
SalesFormLetter_Invoice salesFormLetter_Invoice;
SalesParmUpdate salesParmUpdate;
Notes ret = “Failed”;
#OCCRetryCount
ttsBegin;
//delete_from salesParmUpdate;
//delete_from salesParmTable;
//delete_from salesParmLine;
while select forUpdate salesParmUpdate
{
salesParmUpdate.delete();
}
while select forUpdate salesParmTable
{
salesParmTable.delete();
}
while select forUpdate salesParmLine
{
salesParmLine.delete();
}
ttsCommit;
try
{
ttsBegin;
select * from wlkSalesStageTable where wlkSalesStageTable.SubJobNo == _subJobNo &&
wlkSalesStageTable.InvoiceNo == _invoiceNo &&
wlkSalesStageTable.Type == WLKSalesStageTableType::Invoice &&
wlkSalesStageTable.Status == WLKIntegrationStatus::Success;
select forUpdate salesTable where salesTable.SalesId == _subJobNo;
{
salesTable.CurrencyCode = wlkSalesStageTable.Currency;
salesTable.FixedExchRate = wlkSalesStageTable.ExchangeRate;
salesTable.InvoiceAccount = wlkSalesStageTable.CustomerCode;
salesTable.CITJobStatus = CITJobStatus::InProgress;
salesTable.CITDateofSupply = wlkSalesStageTable.DateOfSupply;
salesTable.editFixedExchRate(false,wlkSalesStageTable.ExchangeRate);
salesTable.convertCurrencyCode(salesTable.CurrencyCode,TradeCurencyConversion::ExchRate);
salesTable.FixedExchRate = wlkSalesStageTable.ExchangeRate * 100;
salesTable.Update();
while select wlkSalesLineStageTable
where wlkSalesLineStageTable.SubJobNo == _subJobNo
&& wlkSalesLineStageTable.InvoiceNo == _invoiceNo
&& wlkSalesLineStageTable.Type == WLKSalesStageTableType::Invoice
&& wlkSalesLineStageTable.Status == WLKIntegrationStatus::Success
{
select forUpdate _salesLine where _salesLine.SalesId == salesTable.SalesId
&& _salesLine.ItemId == wlkSalesLineStageTable.ChargeCode
&& _salesLine.TaxItemGroup == wlkSalesLineStageTable.ChargeSalesTaxCode
&& _salesLine.SalesStatus != SalesStatus::Invoiced
&& _salesLine.SalesStatus != SalesStatus::Canceled;
if(_salesLine)
{
_salesLine.reread();
_salesLine.SalesPrice = wlkSalesLineStageTable.UnitPrice;
_salesLine.LineAmount = wlkSalesLineStageTable.UnitPrice;
_salesLine.update();
}
else
{
service = new WLKSalesLineStageService();
service.createSalesLine(wlkSalesLineStageTable);
}
}
}
salesFormLetter_Invoice = salesFormLetter_Invoice::newInvoice();
salesFormLetter_Invoice.initParameters(salesParmUpdate, Printout::Current, NoYes::No, NoYes::No, NoYes::No, NoYes::No);
salesFormLetter_Invoice.createParmUpdateFromParmUpdateRecord(
SalesFormletterParmData::initSalesParmUpdateFormletter(DocumentStatus::Invoice,salesFormLetter_Invoice.pack()));
salesParmUpdate = salesFormLetter_Invoice.salesParmUpdate();
salesParmUpdate.DocumentStatus = DocumentStatus::Invoice;
salesParmUpdate.SpecQty = SalesUpdate::All;
salesParmUpdate.Proforma = NoYes::No;
salesParmUpdate.ReduceOnHand = NoYes::No;
salesParmUpdate.CheckCreditMax = TypeOfCreditmaxCheck::BalanceAll;
salesParmUpdate.Storno = NoYes::No;
salesParmUpdate.CreditRemaining = NoYes::Yes;
salesParmUpdate.SumBy = AccountOrder::None;
salesFormLetter_Invoice.salesParmUpdate(salesParmUpdate);
salesFormLetter_Invoice.reArrangeNow(false);
axSalesParmTable = AxSalesParmTable::construct();
axSalesParmTable.parmParmId(salesFormLetter_Invoice.parmId());
axSalesParmTable.parmSalesId(salesTable.SalesId);
axSalesParmTable.parmCITInvoiceId(_invoiceNo);
axSalesParmTable.parmInvoiceAccount(wlkSalesStageTable.CustomerCode);
axSalesParmTable.parmOrdering(DocumentStatus::Invoice);
axSalesParmTable.parmTransdate(today());
axSalesParmTable.parmDocumentDate(today());
axSalesParmTable.defaulting(true);
axSalesParmTable.save();
select firstOnly createdTransactionId
from wlkSalesLineStageTable order by CreatedTransactionId desc
where wlkSalesLineStageTable.SubJobNo == _subJobNo
&& wlkSalesLineStageTable.InvoiceNo == _invoiceNo
&& wlkSalesLineStageTable.Type == WLKSalesStageTableType::Invoice
&& wlkSalesLineStageTable.Status == WLKIntegrationStatus::Success;
createdTransactionId = wlkSalesLineStageTable.createdTransactionId;
while select wlkSalesLineStageTable
where wlkSalesLineStageTable.SubJobNo == _subJobNo
&& wlkSalesLineStageTable.InvoiceNo == _invoiceNo
&& wlkSalesLineStageTable.Type == WLKSalesStageTableType::Invoice
&& wlkSalesLineStageTable.Status == WLKIntegrationStatus::Success
&& wlkSalesLineStageTable.createdTransactionId == createdTransactionId
{
select salesLine
where salesLine.ItemId == wlkSalesLineStageTable.ChargeCode
&& salesLine.SalesId == wlkSalesLineStageTable.SubJobNo
&& salesLine.TaxItemGroup == wlkSalesLineStageTable.ChargeSalesTaxCode
&& salesLine.SalesStatus != SalesStatus::Invoiced
&& salesLine.SalesStatus != SalesStatus::Canceled;
if(salesLine)
{
axSalesParmLine= AxSalesParmLine::construct();
axSalesParmLine.parmParmId(axSalesParmTable.parmParmId());
axSalesParmLine.parmTableRefId(axSalesParmTable.parmTableRefId());
axSalesParmLine.parmItemId(wlkSalesLineStageTable.ChargeCode);
axSalesParmLine.parmRemainBeforeInvent(salesLine.RemainInventPhysical);
axSalesParmLine.parmDeliverNow(wlkSalesLineStageTable.Quantity);
axSalesParmLine.parmInventTransId(salesLine.InventTransId);
axSalesParmLine.parmInventDimId(salesLine.InventDimId);
axSalesParmLine.parmSalesLineRecId(salesLine.RecId);
axSalesParmLine.parmInvoiceAccount(wlkSalesStageTable.CustomerCode);
axSalesParmLine.salesFormLetter(salesFormLetter_Invoice);
axSalesParmLine.save();
}
}
salesFormLetter_Invoice.run();
ret = “Success”;
ttsCommit;
}
catch(Exception::Error)
{
ttsBegin;
update_recordSet upWlkSalesTabStg setting status = WLKIntegrationStatus::Error
where upWlkSalesTabStg.InvoiceNo == _invoiceNo;
ttsCommit;
}
catch (Exception::Deadlock)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::Deadlock;
}
else
{
retry;
}
}
catch (Exception::UpdateConflict)
{
// try to resolve update conflict
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
catch(Exception::DuplicateKeyException)
{
// retry in case of an duplicate key conflict
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::DuplicateKeyExceptionNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::DuplicateKeyException;
}
}
return ret;
}