A Deadlock occurs when multiple Azure SQL Managed Instance sessions request conflicting locks in such a way that two locks are blocked by each other. The simplest example is with two sessions. Imagine the following sequence of events:
- Session 1 updates Row1
- Session 2 updates Row2
- Session 1 tries to update Row2 - it waits because the row is locked by Session 2
- Session 2 tries to update Row1 - it waits because the row is locked by Session 1
Here we have Session 1 waiting on a lock held by Session 2 and Session 2 waiting on one held by Session 1. In this example, both sessions would wait forever, so Azure SQL Managed Instance chooses one of them as the deadlock victim, kills their connection and rolls back their updates so that the other can continue. The user that is rolled back will often receive an error message.
The example above is the simplest type of deadlock, involving only two sessions. However, deadlocks can involve many more sessions, and it can be very difficult to track down the root cause of all the trouble. Spotlight Enterprise provides some features to make this task easier.
When the alarm is raised the SQL Server | SQL Activity Drilldown | Locks page | Lock Types chart | Deadlocks/sec counter shows you how frequently deadlocks are occurring in your system.