AzureFeeds

Recently I have worked on cases where it is observed that data sync fails with deadlock error.

Error:

Database re-provisioning failed with the exception ‘Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To investigate deadlocks, you can enable Extended Events by following the guidance provided here:

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=event-file#collect-deadlock-graphs-in-azure-sql-database-with-extended-events

A Sample deadlock graph looks below:

Running the Data Sync Health Checker report also results in the same error being observed.

https://github.com/microsoft/AzureSQLDataSyncHealthChecker

Database re-provisioning failed with the exception ‘Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Explanation:

When a deadlock occurs between a Data Sync operation and a customer transaction, the Data Sync operation is always selected as the victim.

To mitigate this issue without removing and recreating the Data Sync configuration, please follow the below steps:

• Remove the table referenced in the deadlock error from the Data Sync configuration and then restart the synchronization.


• If automatic synchronization is enabled, disable the schedule and run the sync process manually.


• Increase the frequency of the synchronization process to minimize overlapping operations and reduce the likelihood of deadlocks.

Additionally, running UPDATE STATISTICS and rebuilding indexes on the system tables involved in Data Sync may help improve performance. This can assist the SQL optimizer in selecting a more efficient execution plan, thereby allowing the synchronization process to complete faster and reducing the likelihood of deadlocks.

Please note that Azure SQL Data Sync will be retired on September 30, 2027.

For more details, refer to the official documentation:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql