Rod Edwards looks at deadlocks:
I’ve also included a REALLY useful bit of functionality in a common free tool that you may not expect. Simply scroll to the to the end for it if you’re up to speed with Deadlocks already, or don’t want to read my inane rambles.
So we know what a deadlock is, multiple queries all contending for resources, one query holds some resources and is waiting on resources that another query has. That second query isn’t giving the resources up and is waiting for resources that the first query has. Both are stubborn, neither are releasing what they have until they completed…so we have a standoff.
Read on to learn a bit more about the mechanics behind deadlocks, as well as how Plan Explorer makes life easier.