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!

Some references:
Query Hint (T-SQL)
Appropriate Uses of Parallelism in SQL Server
Detecting and Ending Deadlocks
Deadlock Troubleshooting (Part 1)