Deadlock

A SQL Server condition where two or more sessions each hold a lock that another session needs, creating a circular dependency that SQL Server resolves by terminating one session, known as the deadlock victim.

• Deadlock detection: SQL Server’s deadlock monitor automatically detects circular lock dependencies and terminates the session with the lowest accumulated transaction cost, rolling back its transaction and returning error 1205.
• Deadlock victim: The terminated session receives SQL Server error 1205. Applications should be designed to catch this error and automatically retry the affected transaction when appropriate.
• Deadlock analysis: DBAs can capture deadlock information using the default System Health Extended Events session, which records deadlock graphs automatically, or by creating a custom Extended Events session with the xml_deadlock_report event.
• Deadlock visualization: SQL Server Management Studio (SSMS) can display deadlock graph XML as a visual diagram, showing the participating sessions, locked resources, and lock types involved in the deadlock.
• Prevention strategies: Common techniques for preventing deadlocks include acquiring locks in a consistent order, keeping transactions as short as possible, creating appropriate indexes to reduce lock duration and scope, and using READ COMMITTED SNAPSHOT ISOLATION (RCSI) to eliminate many read-write deadlocks.
• Relevant Idera tools: SQL Diagnostic Manager captures deadlock graphs and tracks deadlock frequency trends, helping DBAs proactively identify and resolve recurring issues.
• Related terms: Blocking, Latch Wait, Isolation Level, Extended Events, Error Handling.