Can SELECT Statements Cause Blocking to Occur in SQL Server?
The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.
The answer is, absolutely! SELECT statements acquire a shared lock on the tables being accessed. This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked. When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.
This default behavior can be modified by using one of many query hints. Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it. When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries. Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.
One additional caveat about both methods. If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction. There is an interresting post on this topic that can be found here.