What does WITH (NOLOCK) actually mean

Contrary to popular belief WITH (NOLOCK) isn’t actually the SQL Server “Turbo” button.  While it may appear that way, there’s actually some stuff going on in the back that makes it look this way.  Understanding what is happening is critical to using WITH (NOLOCK) correctly.

When you use the NOLOCK query hint you are telling the storage engine that you want to access the data no matter if the data is locked by another process or not.  This is why is can make it appear that the query is just running faster as you are no longer waiting for other processes to complete their writes, you are simply reading what ever is in the buffer pool or on disk at the time that you get to it.  This leads to a problem called dirty reads, meaning that you may not be getting the same values that you would get it you were to run the query again.  This isn’t necessarily a bad thing, just something to be aware of.  Usage of the NOLOCK hint may be just fine in your application, or it may be incredibly bad.

When other users are making changes (insert, updates and deletes all do this) to the data in the tables within the database, they take locks on the pages which they are modifying.  These locks tell the SQL Server that no one else can use these pages until the change has been completed.  Once these locks have been released your select statement can continue until it either finishes or comes to another page which is locked.

When using the NOLOCK hint you ignore the locks and read what ever data is available at the time.  This can cause problems when joining foreign keys as you can end up with different data being returned if the foreign key value is updated (depending on the execution plan that your SELECT statement uses).

Many people believe that one reason that NOLOCK works is because the execution plan changes when you use the NOLOCK hint.  This isn’t true.  The NOLOCK tuning hint isn’t used by the query optimizer, but instead by the data access engine as this isn’t used to change the way that the data is found, but instead it simply changes the way the locks are accessed as the data is read from the buffer cache and/or the disk.

Hopefully this will give a little insight into what is happening when you use the “SQL Turbo button”.

Denny