Applies to Product - Microsoft Dataverse
What’s happening?
The customer is experiencing a database deadlock in SQL Server for the PrincipalObjectAccess table when executing an SSIS package for the account entity in Microsoft Dynamics 365 CE.
Reason:
This is related to the execution of the SSIS package, which performs insert and update operations on the account entity. The deadlock occurs despite the presence of retry mechanisms in both Dynamics 365 and SQL Server, indicating that the deadlock is not being resolved automatically.
Resolution:
- Ensure that the KingswaySoft integration tool is updated to the latest version.
- In a lower environment, confirm if the "CreateMultiple" or "UpdateMultiple" operations are available, as these have been observed to perform faster than using only Create or Update.
- Use bulk operation messages as per the guidelines provided in the Power Apps documentation.
- After implementing the above steps, trigger a new job using a batch size of 1 and monitor for any significant differences in performance.
- Follow best practices regarding the management of the PrincipalObjectAccess table as outlined in the relevant documentation.