I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:
Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?
If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.
Can anyone explain this? Or should I bounce my server and hope it never happens again?
Although the (NOLOCK) hint is puzzling, it is possible to deadlock on a single SELECT. I replicated this behavior about 12 years ago, but can't remember the specific scenario other than it was happening on a wide table (I think there was one row to a page, 2K pages) and the deadlock happened on the index.
To see where the deadlock is occuring, you may want to try:
Select foo From v_bar WITH (NOLOCK)
Connection 2 (immediately after executing 1)
Check the errorlog to see the results of the trace and read up on Troubleshooting Deadlocks in SQL BOL.