Transaction deadlock for select query

Question

Occasionally, I have the following error for a stored procedure which is only a Select query: Transaction (Process ID 91) was deadlocked on lock

My initial understanding was that a select query won't lock a table, or won't cause a deadlock even if the table it tries to query is being updated/locked by another process, but it seems that a select query can cause deadlocks as well.

If I set the isolation level to read uncommitted for the query, will that solve the problem?

Answer 1

My init understanding is that a Select query won't lock a table, or won't cause a deadlock

This understanding is wrong. SELECT queries take shared locks on the rows they analyze. Shared locks may conflict exclusive locks from update/delete/insert statements. Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE. When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw.

As with any deadlock, you need to post the exact schema of the tables involved, the exact T-SQL statements and the deadlock graph. See How to: Save Deadlock Graphs (SQL Server Profiler). With this information you can receive guidance how to fix the deadlock.

Answer 2

Like Remus says, you are getting the deadlocks because SELECT and UPDATE (or other) operations deadlocking each other, not SELECT vs SELECT. You will have to look at all your queries touching that table and create proper covering indexes for those queries and that will solve your problems. Good covering indexes is the preferred solution rather than using WITH (NOLOCK) table hints.

See the following link for a good tutorial on how to create covering indexes and how it affects deadlocks.