SQL Server SELECT statements causing blocking

Question

We're using a SQL Server 2005 database (no row versioning) with a huge select statement, and we're seeing it block other statements from running (seen using sp_who2). I didn't realise SELECT statements could cause blocking - is there anything I can do to mitigate this?

Answer 1

SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.

If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).

Answer 2

From documentation:

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

A shared lock is compatible with another shared lock or an update lock, but not with an exlusive lock.

That means that your SELECT queries will block UPDATE and INSERT queries and vice versa.

A SELECT query will place a temporary shared lock when it reads a block of values from the table, and remove it when it done reading.

For the time the lock exists, you will not be able to do anything with the data in the locked area.

Two SELECT queries will never block each other (unless they are SELECT FOR UPDATE)

You can enable SNAPSHOT isolation level on your database and use it, but note that it will not prevent UPDATE queries from being locked by SELECT queries (which seems to be your case).

It, though, will prevent SELECT queries from being locked by UPDATE.

Also note that SQL Server, unlike Oracle, uses lock manager and keeps it locks in an in-memory linked list.

That means that under heavy load, the mere fact of placing and removing a lock may be slow, since the linked list should itself be locked by the transaction thread.