In my current applications, we were getting from time to time (let's say 3 or 4 times a month) a deadlock problem.
As a consequence, we were receiving the nice message "Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction".
OK but how can we trace it? How could we reproduce the problem? How could we get any clue about that?
I spent a lots of time 
and 
and tearing my hairs out before I find out the solution that will help me. Quite simple indeed but I was just not knowing it.
Creating Deadlock Graph via SQL Server Profiler
The SQL Server profiler tool includes a nice tool that will detect deadlock graphes and let us help finding where the problem comes from.
-
Find kind of test scenario that lets you reproduce the problem more frequently
-
Run Sql Server Profiler tool
-
Click on File / New Trace
-
Connect to the DB that you will target when reproducing the problem
-
Click on "Events Selection" and check "Show all events"
-
In the "Locks" category, check "Deadlock Graph"
-
Click Run
You can now run your stress test and patiently wait it will crash with the classical "deadlock victim" exception. And now what else ?
Analysing Deadlock Graph
Opening the graph in Sql Server will give you something like : (Click on the image to enlarge it)
Well I'm not a DBA or a DB expert but it's quite easy to see that there is indeed a deadlock or circular dependency problem.
When hoovering on the processes, it show up the statement (UPDATE, INSERT, ...) or the stored procedure objectId. Looking in sys.all_objects gives you the stored proc responsible for that.
But do not forget to open the graph file in text mode !
Indeed, you will see in there clearly which line of the stored proc is guilty !
More infos ?
Here are some links that helped me understanding a bit more what was happening.
Here are some explanations about deadlocks in MSDN
Mike Dimmick gives also a very clear explanation on http://mikedimmick.blogspot.com/2004/03/selectupdate-problem-or-why-updlock.html