A deadlock is caused when two or more transactions hold locks that the other transactions require. Blue Matador detects deadlocks in Azure SQL by monitoring the deadlock metric on your SQL databases.
Effects
Deadlocks will prevent the involved transactions from completing. When a deadlock is detected, the server will abort one of the involved transactions, rolling it back, and allowing the other transactions to proceed. The aborted transaction must be retried.
Here is an example of the response returned from a transaction that is aborted due to deadlock:
Failed to execute query. Error: Transaction (Process ID 129) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Fixes
Unfortunately, deadlocks can be difficult to troubleshoot. If deadlocks are consistently happening, you can query the following system databases to view currently active transactions to try and figure out which one is causing the deadlock, but this method can be tedious on databases with many active transactions:
To view deadlocks that have occurred in the past, you can query the deadlock events from the Azure SQL master database by following this blog post.
The best defense against deadlocks is to catch deadlocks in your application and log enough information about the transaction so that you can determine which parts of your application are prone to deadlocks when they do occur. Keep in mind that the transaction which gets aborted due to a deadlock may not be the cause of deadlocks if there are other transactions on the same tables. In general, you can reduce the impact of deadlocks with these techniques:
- Split longer transactions into smaller ones
- Avoid executing application code while holding open a lock
- Ensure the application acquires locks in a consistent order
