Anatomy of a Deadlock
Deadlocks come in two flavors: cycle deadlocks and conversion deadlocks. Both types arise when processes contend for the same resources. Cycle deadlocks arise in contention for data, either a row or a table. Conversion deadlocks occur over contention for lock escalation, where two processes are in competition for exclusivity.
In a cycle deadlock, Process A wants a resource that is already locked by Process B, and Process B wants the resource already locked by Process A. Each process waits for the other to release its lock on the resource it's waiting for, but because both processes hold the lock on the resource requested by the other, they wind up waiting forever.
Both types, especially conversion deadlocks, can materialize from relationships that are not immediately obvious. Thankfully, the tools that ship with SQL Server and Windows 2000 are very helpful in identifying deadlock messages from the server. You can use the Create Trace Wizard and choose "Identify causes of a deadlock" or "Profile the performance of a stored procedure" to see what happened and how.
The server identifies deadlocks by raising error 1205. The server messages can be turned on and redirected by trace flags. The relevant trace flags are 1204 (for deadlock detection), 3604, and 3605. Trace 1204 gives voluminous information on the participants in the deadlock and provides a clue as to what type of deadlock you are clearing. Trace 3604 sends messages to Query Analyzer; trace flag 3605 redirects 1204's output to the SQL Server error log.
Once the source for contention is known, you can determine which fix to apply or even elect to live with the deadlock, which also is an option. If you decide to live with the deadlock condition, you'll have your application retry the operation after a 1205 error.
As an exercise to see how deadlocks come about, you can make a deadlock happen in Query Analyzer. Start a transaction in one session that will do an update on resource A (say, change an order number in an order header of Northwind). Open another Query Analyzer window and start another transaction on the same resource you decided to modify, but pick a different field (say, date). Go back to the first window. In the running transaction, run the command to also update date. You will succeed in tying the server in knots and the server will return the 1205 error.