Tag Archives: Cannot select a record in Change Tracking Version (AifSqlCtVersion)

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

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