RSS

Category Archives: Dynamics Connector

Deadlocks show up in AIF exception log when CRM synchronizes multiple companies.


I was working on a case where some issues occured when trying to synhronize data between MS dynamics ax 2012 and MS CRM.

Scenario description:

In MS CRM the following maps have been set up for synchronization in two integrations.

CRM_maps

Each integration uses its own AX user for connection.

From the CRM point of view, the synchronization seems to succeed. However going through the exception log in AX it seems that quite a few deadlocks have occurred, please see screendump below:

AX_deadlocks

And here’s a screendump from the “general” tab showing the error message – “Cannot select a record in Change Tracking Version (AifSqlCtVersion). Change Tracking Version: 0.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.
”:

AX_deadlock_general

The deadlocks seems to have disappeared by themselves.
From this blog post this issue seems to be a known issue – https://community.dynamics.com/ax/f/33/t/160640 – however the ax database had not recently been updated.

 

The cause:

The cause seems to be the SQL optimiser doing a clustered index scan (RecId) in the query below, resulting in blocking:

SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)

 

The solution:

  • Login to Dynamics AX –> Open a Development workspace, and navigate to AOT –> Dictionary -> tables,
    Find the table AIFSQLCTVERSION , and create a new non-unique index for CTVERSION field
  • Login to SQL server Management studio, and create a new plan guide for the query adding index hint
    EXEC sp_create_plan_guide @name = N'[PlanGuide-AIFSQLCTVERSION]’, @stmt = N’SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)’, @type = N’SQL’, @module_or_batch = N’SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)’, @params = N’@P1 bigint’, @hints = N’OPTION (TABLE HINT (T1, INDEX( I_100021KOO_CTVERSIONIDX), UPDLOCK))’
    GO(replace the index name I_100021KOO_CTVERSIONIDX with the actual name of the newly created index)
 
 
 
All About Dynamics 365

Dynamics 365, D365, Implementor

syedrafayali

This blog contains information about Functional techniques and guidelines in Microsoft Dynamics AX, including tips, tricks, tutorials, tools and upcoming news enhancement in Microsoft Dynamics Ax

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Finite Minds

Adventures in IoT

Dynamics Ax

Technical Knowledge

timsaxblog

A blog about implementing Microsoft Dynamics AX and Dynamics 365 for Operations

Microsoft Dynamics 365 Blog

All Things Dynamics 365, A Blog by sandeep chaudhury

DEVSerra - Dynamics AX development blog

Your official Microsoft Dynamics AX blog.

OrganicAX

Discovering Dynamics

AX

A blog by Hai Nguyen

Learn Dynamics Ax with Johnkrish

Live as if you were to die tomorrow. Learn as if you were to live forever - Mahatma Gandhi ****** The more I learn, the less I know - Albert Einstein

Twisted Untwirled

Just another WordPress.com site

ramdynamicsax

Just another WordPress.com site

guyterry's Dynamics AX blog

Just another Dynamics AX blog

Chaitanya kumar

Happy DAXing...!!! :)