Things You Need To Know about Nolock

Written By: Matan Yungman 26/10/2015

Nolock is not the Satan, but no an angel too

Surfing the web you will find articles saying that Nolock is dangerous and shouldn’t be used, and articles stating that it’s THE solution for many problems. The truth, as in many cases, lies in the middle.

If you have a blocking problem and you think about working with Nolock, analyze whether you can encounter dirty reads and whether you can live with the implications of them. If you can live with it, remember that Nolock is not the Satan. However, keep in mind the implications, and that in many cases, there are other solutions like improving query performance and working with Snapshot or Read Committed Snapshot isolation levels.

Nolock can cause Inconsistency is more ways than what you may think

Nolock doesn’t only mean you can have dirty reads, but actually also that you can read the same row multiple times or skip rows. This scenario is pretty rare, but it can still happen. Don’t believe me? Read Itzik Ben-Gan’s post.

Nolock doesn’t really mean “No Lock”

Nolock actually does place a lock on the table – a Schema Stability lock (Sch-S). It doesn’t affect DML operations, but it does affect operations that change the table schema like dropping or adding columns and partition switch. Which leads us to the fact that..

Nolock doesn’t stand in line

Imagine you’re in your car, waiting for the traffic light to turn green. Each time it does, a group of crazy motorcycles passes you from the right, blocks the junction and prevents you from entering the junction until the traffic light switches back from green to blue. You end up unable to enter the junction for 30 minutes. This is what Nolock does to Partition Switch.

Up until SQL Server 2012, Nolock didn’t stand in line. This meant that he passed everyone else and put a Schema Stability lock on the table. As stated earlier, this didn’t affect most operations, but it was a problem for partitioned tables with high level of activity and many or lengthy Nolock queries.

This was fixed in SQL Server 2012 and since then Nolock stands in line, but if you’re not on SQL Server 2012 and up yet, take this into account.