Transaction deadlock for select query

sql tsql concurrency deadlock
peanut picture peanut · Apr 29, 2011 · Viewed 51.8k times · Source

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

Remus Rusanu picture Remus Rusanu · Apr 29, 2011

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.

Related questions

SET NOCOUNT ON usage

Inspired by this question where there are differing views on SET NOCOUNT... Should we use SET NOCOUNT ON for SQL Server? If not, why not? What it does Edit 6, on 22 Jul 2011 It suppresses the "xx rows affected" message after any …

Read More
Only inserting a row if it's not already there

I had always used something similar to the following to achieve it: INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WHERE PrimaryKey = @primaryKey) ...but once under load, a primary key violation occurred. This is …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More