Identifying causes for deadlock and ways to prevent it

Question

Hi All,

We have an ELT process wherein we transfer data from Azure Data Factory into a staging table present in Azure SQL DB and then trigger Stored Procedure that alters schema from staging to the final version.

The above process executes parallelly for multiple entities(at the same time)

But sometimes the job fails intermittently with failure message as deadlock.

Note -- We have enabled Azure Log Analytics for Azure SQL Db logs.

So, i have below set of queries:

1. Are there any metadata queries like sys.logs to determine the cause of deadlock and jobs getting executed at that instance.
2. How to identify the exact time and the cause of deadlock leveraging Azure log analytics (KQL queries)
3. ALTER SCHEMA uses a schema level lock. So, my understanding is the deadlock might be due to this reason but is there a sure shot way to confirm this is the cause for deadlock as the failures are intermittent and not always.
4. Is there any sys. queries to identify whether the schema is in lock state or not, so we can check the state of schema before altering it in case of parallel executions assuming #3 is the cause.
5. Is there any correlation between deadlock occurrence and database tier? Because we observed that after increasing the DB tier, the intermittent failure frequency reduced.

@Alberto Morillo @Ronen Ariely @Erland Sommarskog

Answer 1

Priya,

In my current job I have to troubleshoot some ETL packages when they fail to execute or when they keep executing for hours and days. Please make sure synchronous statistics updates, index and stats maintenance when ETL jobs are running. Here you will find how updates to stats originated by SQL are considered schema changes. These types of events and others can be considered schema changes and can create blocking and can make BULK INSERT operations to fail. You can use Extended Events to capture this type of activity.

Deadlocks are not related to the Azure SQL service tier and can occur on big databases and small databases as well. Deadlocks are the result of application code combined with a database schema resulting in access patterns which lead to a cyclical dependency.

In the scenario you described make sure the ETL process creates long transactions. Verify constraints including foreign key constraints are not involved.