web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Cannot delete a record...
Microsoft Dynamics AX (Archived)

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.

(0) ShareShare
ReportReport
Posted on by Sathish_Chinnappan 1,318

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;
}

*This post is locked for comments

I have the same question (0)
  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Why do you need this section ? And you obviously do not have try catch around it.

    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;


  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,146 Super User 2025 Season 2 on at

    Hi Sathish,

    If you use the rich formatting option, you can use a code applet '</>' which makes the coding more readable.

    Why are you deleting all records from the salesparm tables? These contain settings also for other sales order actions, started by other users. Probably this is causing the issue, but not sure. Have you started debugging to see which exact part is raising the error?

  • Sathish_Chinnappan Profile Picture
    Sathish_Chinnappan 1,318 on at

    Hi Guys,

    Thanks for the reply.

    I have removed the delete of SalesParmUpdate now.

    Now again while doing load test, got below error :

    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.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,146 Super User 2025 Season 2 on at

    Hi Sathish,

    Did you use the debugger already to see what is actually going on? Based on the blocking and blocked SQL statement, can you then find the related x++ coding?  

  • Sathish_Chinnappan Profile Picture
    Sathish_Chinnappan 1,318 on at

    I cant find where exactly error is coming ,its a integration from other application so cant debug it.

  • Sathish_Chinnappan Profile Picture
    Sathish_Chinnappan 1,318 on at

    if 4 sales orders posting 1 got success and remaining getting deadlock error. so cant debug while multiple users doing

  • Sathish_Chinnappan Profile Picture
    Sathish_Chinnappan 1,318 on at

    below is the sql log of deadlock:

    04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab6207fc28 mode=U requestType=wait

    04/16/2019 17:06:40,spid27s,Unknown,waiter-list

    04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab62077c28 mode=X

    04/16/2019 17:06:40,spid27s,Unknown,owner-list

    04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a663dc0900 mode=X associatedObjectId=72057597066412032

    04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab62077c28 mode=U requestType=wait

    04/16/2019 17:06:40,spid27s,Unknown,waiter-list

    04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab6207fc28 mode=X

    04/16/2019 17:06:40,spid27s,Unknown,owner-list

    04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a911084900 mode=X associatedObjectId=72057597066412032

    04/16/2019 17:06:40,spid27s,Unknown,resource-list

    04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)<c/>@P2 int<c/>@P3 bigint<c/>@P4 nvarchar(5)<c/>@P5 nvarchar(21)<c/>@P6 nvarchar(21)<c/>@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1<c/>RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

    04/16/2019 17:06:40,spid27s,Unknown,inputbuf

    04/16/2019 17:06:40,spid27s,Unknown,unknown

    04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    04/16/2019 17:06:40,spid27s,Unknown,unknown

    04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

    04/16/2019 17:06:40,spid27s,Unknown,executionStack

    04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab6207fc28 taskpriority=0 logused=5748 waitresource=KEY: 7:72057597066412032 (ef20a890fc75) waittime=6278 ownerId=155027538 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.620 XDES=0x2ab626b0430 lockMode=U schedulerid=13 kpid=5692 status=suspended spid=125 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027538 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)<c/>@P2 int<c/>@P3 bigint<c/>@P4 nvarchar(5)<c/>@P5 nvarchar(21)<c/>@P6 nvarchar(21)<c/>@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1<c/>RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

    04/16/2019 17:06:40,spid27s,Unknown,inputbuf

    04/16/2019 17:06:40,spid27s,Unknown,unknown

    04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    04/16/2019 17:06:40,spid27s,Unknown,unknown

    04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

    04/16/2019 17:06:40,spid27s,Unknown,executionStack

    04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab62077c28 taskpriority=0 logused=5124 waitresource=KEY: 7:72057597066412032 (b61e207e9d40) waittime=6278 ownerId=155027557 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.623 XDES=0x2a8a2824430 lockMode=U schedulerid=12 kpid=1004 status=suspended spid=120 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027557 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    04/16/2019 17:06:40,spid27s,Unknown,process-list

    04/16/2019 17:06:40,spid27s,Unknown,deadlock victim=process2ab62077c28

    04/16/2019 17:06:40,spid27s,Unknown,deadlock-list

    Please Suggest.

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Can you confirm that data you use for each session is deterrent ? Otherwise, if multiple sessions try to invoice same orders you will get these errors and it's ok.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans