Resolving Deadlocks in SQL Server 2000

Deadlocking can be a difficult problem in a multi-user SQL Server application. Deadlocks are caused when transactions mutually block each other, and each is waiting for the other to finish. SQL Server will detect deadlocks involving locked database resources and cancel one of the queries, and roll back the transaction.

Follow the link to gain more knwoldge on this topic:
http://www.code-magazine.com/Article.aspx?quickid=0309101