Philippsen's Blog

Everyday findings in my world of .net and related stuff

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

Posted by Torben M. Philippsen on January 25, 2016

Recently 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)

 

Advertisements

Sorry, the comment form is closed at this time.

 
%d bloggers like this: