Dynamics Community Forum Thread Details

AX not working and sql raised deadlock

when trying to run ax for the all the user ax hang and not respond at all after trying to find what going on

i find using sql performance dashboard that a query raise deadlock

and here the statment

Select RECID, PARTITION, TRANSACTIONCURRENCYAMOUNT, ACCOUNTINGCURRENCYAMOUNT, REPORTINGCURRENCYAMOUNT,
QUANTITY, ISCREDIT, TRANSACTIONCURRENCYCODE, CURRENCYNAME, CURRENCYSYMBOL, PAYMENTREFERENCE, POSTINGTYPE, TEXT,
GENERALJOURNALENTRY, REASONREF, LEDGERDIMENSION,
ACCOUNTINGDATE, JOURNALNUMBER, POSTINGLAYER, LEDGER, FISCALCALENDARPERIOD, ACKNOWLEDGEMENTDATE,
DOCUMENTDATE, LEDGERPOSTINGJOURNAL, DOCUMENTNUMBER, JOURNALCATEGORY,
REASON, REASONCOMMENT,
CONSOLIDATEDCOMPANY, ISBRIDGINGPOSTING,
JOURNALDESCRIPTION, JOURNALTYPE, JOURNALNAME, SUBLEDGERVOUCHER, TRACENUM, REVERSED, REVERSEENTRY,
MODIFIEDDATETIME, MODIFIEDBY, JOURNALENTRYDATE, HISTORICALEXCHANGERATEDATE, VOUCHER_CN, REVERSED_CN, ID, CREATEDBY FROM (SELECT GJAE.RECID, GJAE.PARTITION as PARTITION, GJAE.TRANSACTIONCURRENCYAMOUNT, GJAE.ACCOUNTINGCURRENCYAMOUNT, GJAE.REPORTINGCURRENCYAMOUNT,
GJAE.QUANTITY, GJAE.ISCREDIT, GJAE.TRANSACTIONCURRENCYCODE, C.TXT AS CURRENCYNAME, C.SYMBOL AS CURRENCYSYMBOL, GJAE.PAYMENTREFERENCE, GJAE.POSTINGTYPE, GJAE.TEXT,
GJAE.GENERALJOURNALENTRY, GJAE.REASONREF, GJAE.LEDGERDIMENSION,
GJE.ACCOUNTINGDATE, GJE.JOURNALNUMBER, GJE.POSTINGLAYER, GJE.LEDGER, GJE.FISCALCALENDARPERIOD, GJE.ACKNOWLEDGEMENTDATE,
GJE.DOCUMENTDATE, GJE.LEDGERPOSTINGJOURNAL, GJE.DOCUMENTNUMBER, GJE.JOURNALCATEGORY,
RTR.REASON, RTR.REASONCOMMENT,
LE.CONSOLIDATEDCOMPANY, LE.ISBRIDGINGPOSTING,
LJT.NAME as JOURNALDESCRIPTION, LJT.JOURNALTYPE, LJT.JOURNALNAME,
TRT.TRACENUM, TRT.REVERSED, TL.CREATEDBY, LJT.MODIFIEDDATETIME, LJT.MODIFIEDBY, LJT.POSTEDDATETIME as JOURNALENTRYDATE, GJAE.HISTORICALEXCHANGERATEDATE, GJEW.VOUCHER_CN, GJEW.REVERSED_CN, LVTCN.ID, GJE.SUBLEDGERVOUCHER, LJTR.REVERSEENTRY FROM GENERALJOURNALACCOUNTENTRY GJAE

Are you sure you mean a deadlock? Your description doesn't sound like that.

A deadlock happens when several statements block each other, e.g. transaction A is waiting for a resource locked hold by transaction B and transaction B is waiting for another resource locked by transaction A. The database server detects such a situation and rolled back on of the transactions; a Deadlock exception is raised in F&O. It also means that knowing one of the queries doesn't tell you much; if you're really dealing with a deadlock, you should look at all the statements involved, the locked resources and types of locks.

You mentioned that the applications hangs, not that a deadlock exception was thrown and your statement is a select, not something taking locks, therefore you don't seem to be dealing with a deadlock. It sounds like waiting caused by a slow query. If you agree with that, stop looking for solutions of deadlocks, because that's not what you need. Instead, look at the execution plan of your query to see which part takes the most time. SSMS can also suggest missing indexes. Another useful too is Query store. That reminds me, which version of AX are you using?

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL