There is generally confusion over the effect of the NOLOCK query hint and locking in general when selects are run. 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
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
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.