This is really just a stub post, and should force me to come back and add more detail to the topic.
In a nutshell, be careful if you have parallelism turned ON for your multi-core SQL server as SQL’s internal worker threads can seemingly jam themselves into a deadlock scenario when executing a parallelised (sp?) query.
to disable parallelism, use the OPTION (MAXDOP 1) query hint.
SELECT col1, col2, col3
FROM table1 t1 INNER JOIN table 2 t2 on t1.id = t2.id
WHERE SomeCondition = SomeValue
OPTION (MAXDOP 1)
This will force SQL Server to run the query with a max parallelism of 1 (ie: single thread). Good-bye locks!