SQL-profiler has a great and easy functionality for detecting dead-locks in the database.
Tracing deadlocks
In SQL-profiler lisen to the folowing events
Lock:Deadlock chain
Lock:Deadlock
Deadlock graph
Lock:Cancel
Open the file in an editor and you easy find the locked and locking functions.
With Cygwin
To get a simple to read trace file you can filter it with simple Cygwin commands before you analyze it.
cat "trace_utf8.xml" | grep -P "procname=|deadlock-list|executionStack" | sed -e "s/>/\>/g" | sed -e "s/</\</g"
Fix the lock
The lock appears when a thread needs to update a table and another needs to read it. The easiest way and in several cases a good solution in non exact systems as in CMS or community products a good solution is to let the reading thread read uncommitted values. This can be done by setting isolation level to “Read uncommitted” or hit the SQL query with the “nolock” hint.
Sample of hinting the SQL query with no lock
SELECT pkId FROM tblPage WITH (nolock)
Sample of setting a stored procedure to Isolation Level Read uncommitted
BEGIN
ISOLATION LEVEL READ UNCOMMITTED