Database Reference
In-Depth Information
Summary
As you learned in this chapter, a deadlock is the result of conflicting blocking between processes and is reported
to an application with the error number 1205. You can analyze the cause of a deadlock by collecting the deadlock
information using various resources, but the extended event
Xml_deadlock_report
is probably the best.
You can use a number of techniques to avoid a deadlock; which technique is applicable depends upon the type
of queries executed by the participating sessions, the locks held and requested on the involved resources, and the
business rules governing the degree of isolation required. Generally, you can resolve a deadlock by reconfiguring
the indexes and the transaction isolation levels. However, at times you may need to redesign the application or
automatically reexecute the transaction on a deadlock. Just remember, at its core, deadlocks are a performance
problem and anything you can do to make the queries run faster will help to mitigate, if not eliminate, deadlocks
in your queries.
In the next chapter, I cover the performance aspects of cursors and how to optimize the cost overhead of
using cursors.
