tsql - Understanding SQL Server LOCKS on SELECT queries

Question

I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.

How is that handled by SQL Server? Would a SELECT query block another SELECT query?

I'm using SQL Server 2012 and a Linq-to-SQL DataContext.

(EDIT)

About performance :

• Would a 2nd SELECT have to wait for a 1st SELECT to finish if using a locked SELECT?
• Versus a SELECT WITH (NOLOCK)?

Answer 1

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

So no - one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.