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)
Advertisements
 
 
 
Nishant Rana's Weblog

Everything related to Microsoft .NET Technology

henrikmarx

A practitioner's views on Dynamics 365 for Operations and Finance

Supreme X++

A blog about good and bad development in Microsoft Dynamics AX

Microsoft Dynamics 365

Pavan Kumar Garlapati

Microsoft Dynamics AX/365 Finance and Operations Development Blog

Technical blog about Dynamics AX 2012 and Dynamics 365 for finance and operations

Suvidha-blogpost

Dynamics 365 Blog

vmoskalenko blog

SQL, AX, Windows, Azure

Syed Rafay Ali

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

All About Microsoft Dynamics

Microsoft Dynamics AX, Microsoft Dynamics 365 for Finance and Operations, D365, Retail, SQL

Anitha Eswaran - Dynamics Ax

Microsoft Dynamics Ax blog

AXAPTA Hut

AxaptaHut ax = new AxaptaHut();

All About Dynamics 365

Dynamics 365, D365, Implementor

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Xalentis

All things Blockchain, AI and IoT

Dynamics Ax

Technical Knowledge