A real quickie this evening, and it’s something I discovered yesterday the hard way: using NOLOCK table hints can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by design and also, there’s nothing wrong with it.

A NOLOCK table hint is exactly the same as a READUNCOMMITTED table hint, and I have copied and pasted from Books Online the definition and the scenarios that can cause blocking even with NOLOCK:

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. So what all that means is that regardless whether READUNCOMMITTED or NOLOCK is specified on a query, any concurrent query, or queries executed whilst the previous query has yet to complete, will be blocked if the locks they need to acquire are incompatible. So, what locks are incompatible? Fortunately Books Online comes to the rescue again, with it’s lock compatibility matrix.

Great, but what locks are we looking for w/r/t read only queries for example, and what locks are they incompatible with? Well if you didn’t read the quoted text from BoL above we’re looking for Sch-S type locks. And by checking the matrix above we know that Sch-S and Sch-M type locks are incompatible. I got caught out by this when running a select with nolock on a table whilst trying to split partitions on the same table.

Again Books Online provides more info on this in the section about Schema Locks:

Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table. In fact it was a whole missunderstanding about what nolock will do; it will do dirty reads but will still lock the tables, and that the hint READUNCOMMITTED is a far more accurate description of what is occurring.

For more info, and some excellent demos, I recommend that you watch this video that goes into NOLOCK and what to do about it.