SQL SERVER – A Brief History of Deadlock and Modern Approach of Resolution

A deadlock investigation needs the graph and cooperation between DBAs and developers. I distinguish a blocking wait from a cyclic dependency.

Each cycle participant waits for a resource held by another. SQL Server selects a victim and rolls back its transaction. That session receives error 1205. Selection considers deadlock priority and rollback cost.

Start with retained xml_deadlock_report events in system_health on modern SQL Server. Historical trace flags 1204 and 1222 write formatted error-log information. The 1222 output is not an XML graph. Error 1205 alone is not a complete deadlock report.

1. Lookup-related cycles: inspect access paths and lock resources. A suitable covering index may help, but there is no universal missing-index fix.
2. Different access order: make transactions access shared resources in a consistent order and shorten the transaction where possible. Test any isolation change against correctness requirements.
3. Retained diagnostic tools: a monitoring product can keep graphs and workload context that would otherwise roll out of a small buffer. It still needs interpretation.

Lookup-related deadlocks were common in my customer work. That observation does not establish a 90-percent industry rate. My historical recommendations included Spotlight Cloud and Spotlight Tuning Pack.

Applications may need a bounded retry of the entire transaction when safe. Capture the graph and investigate the underlying cycle. A retry alone does not correct the cause.

A deadlock victim error is not the complete diagnosis, it is a reason to inspect the cycle and transaction design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.