Deadlock In SQL Dynamic AX

(1) Share

Report

Posted on by Community Member

please help I've been handling this error since yesterday

this deadlock effects on application performance to be extremely slow:

"Cannot create a record in DirPartyTable. Deadlocks, where on or more users have simultaneouslu locked the whole table or part of it"

been surf the whole time but cannot find exact solution. Any help would be appreciated.

Best Regards,

Eriko Prasetyo

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Brandon Wiese 17,788 on at

Like (0)

Report

That's a rather unusual table to have deadlocks.

Do you have SQL Server trace flag 1224 enabled?

Have you enabled SQL Server trace flag 1222 to write deadlock information to the error log?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

Sorry for late reply,

This is what it looked like from SQL Statement Trace Log

From description : INSERT INTO DIRPARTYTABLE (INITIALS,CHILDRENNAMES,MARITALSTATUS,HOBBIES,GENDER,NAMESEQUENCE,PHONETICFIRSTNAME,PHONETICMIDDLENAME,PHONETICLASTNAME,PERSONALTITLE,PERSONALSUFFIX,PROFESSIONALTITLE,PROFESSIONALSUFFIX,BIRTHMONTH,BIRTHDAY,BIRTHYEAR,ANNIVERSARYMONTH,ANNIVERSARYDAY,ANNIVERSARYYEAR,COMMUNICATORSIGNIN,NAME,LANGUAGEID,NAMEALIAS,PARTYNUMBER,INSTANCERELATIONTYPE,KNOWNAS,PRIMARYADDRESSLOCATION,PRIMARYCONTACTEMAIL,PRIMARYCONTACTFAX,PRIMARYCONTACTPHONE,PRIMARYCONTACTTELEX,PRIMARYCONTACTURL,MODIFIEDDATETIME,MODIFIEDBY,CREATEDDATETIME,CREATEDBY,RECVERSION,RELATIONTYPE,PARTITION,RECID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Please let me know if anybody come up with something.

Best Regards,

Eriko Prasetyo

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

A deadlock requires several queries blocking each other. Here you can see just one side of the problem; SQL Server logs will tell you what were the other queries, what kind of locks they held on which resources and so on.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dear Martin,

Can u guide on what to discover in SQL Server logs? Especially for my kind of deadlocks.

Thank you,

Best Regards,

Eriko Prasetyo

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

And this is what it looks like from my SQL log, hope it helps:

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

It looks like you have deadlock details there. The logs above the highlighted lines shows details including one of the queries. You'll have to go through quite a few lines; you might find easier to export the log to a file and work with the file.

You can also use other (possibly better) techniques, such as extended events.

Was this reply helpful? Yes No
Suggested answer

Peter Janik 70 on at

Like (0)

Report

You can also use system_health session in the Extended events and filter deadlock events.

Was this reply helpful? Yes No