Deadlock Identification and Resolution - SQL 2000 and 2005
Deadlocking - what is it and why does it happen?
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. Refer to this article which talks in detail about it.
https://msdn2.microsoft.com/en-us/library/ms177433.aspx
Identifying a deadlock
SQL Server provides different ways to identify deadlocks - through profiler trace or by enabling a traceflag. If you enable the traceflag, you can see the deadlock written in the errorlogs.
This article sheds some light on the process of identifying a deadlock and reading the graph.
https://msdn2.microsoft.com/en-us/library/ms178104(SQL.90).aspx
Resolving deadlocks
After having identified a deadlock, it is crucial to understand the sequence of events that lead to it. Refer to these article for troubleshooting steps and some of the best practices recommendations for avoiding deadlocks.
https://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
https://support.microsoft.com/kb/832524/en-us
Special considerations for SQL 2005
SQL Server 2005 provides better concurrency control than SQL 2000 by way of Snapshot Isolation. You might want to review this webcast to learn more about it and see if it can be used in your application design.
Deadlock Troubleshooting - Bart Duncan
Deadlock Troubleshooting, Part 1 https://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
Deadlock Troubleshooting, Part 2 https://blogs.msdn.com/bartd/archive/2006/09/13/751343.aspx
Deadlock Troubleshooting, Part 3 https://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx