A few weeks ago I have presented my Performance Tuning Workshop in Utrecht/Netherlands. On the 3rd day – the most intensive one, where I cover Locking, Blocking, Deadlocking, Latching, Spinlocking – we have also talked about the NOLOCK query hint. Some people always refer to the NOLOCK query hint as the Turbo Booster in SQL Server, because it avoids a huge amount of blocking situations. In this blog posting I want to show you why the NOLOCK query hint is a bad idea.
One of the largest side effects of the NOLOCK query hint is the fact that so-called Dirty Reads can appear in your result set. Let’s have a look at the following code.
BEGIN TRANSACTION UPDATE Person.Person SET FirstName = 'James' WHERE LastName = 'Jones'
As you can see I begin a new transaction, and perform an UPDATE statement against the table Person.Person in the AdventureWorks2012 database. When you now try to read this record concurrently in another session, this SELECT statement will block – in that case the requested Shared Lock is blocked by the already granted Exclusive Lock – the writer blocks the reader.
SELECT FirstName, LastName FROM Person.Person WHERE LastName = 'Jones' GO
Some people apply now the Turbo Booster option in SQL Server and use the query hint NOLOCK:
SELECT FirstName, LastName FROM Person.Person WITH (NOLOCK) -- It's a kind of magic... WHERE LastName = 'Jones' GO
As you can see the blocking situation is immediately resolved and you get back a result from SQL Server – unfortunately you have now a Dirty Read in front of you: you have read uncommitted data. Imagine now that your first transaction with the UPDATE statement is rolled back: in this case you have read data that never ever existed logically in SQL Server!!!
Therefore the query hint NOLOCK doesn’t really make sense in every possible scenario. It *can be* an option if you want to run a report, and you just want to get back a result very fast, and the result doesn’t have to be 100% correct (think about average sales numbers over the day). Of course, when you need a precise result, NOLOCK isn’t really an option. And there are also some specific cases where even NOLOCK can block 😉
Read Committed Snapshot Isolation
One person in Utrecht gave me another good point why the NOLOCK query hint is mostly a no-go option: you can’t switch your database queries in a transparent way to Read Committed Snapshot Isolation (RCSI). I never thought about this specific scenario, but it makes of course sense. Read Committed Snapshot Isolation is a database option. When you enable it *every* query runs in the new optimistic isolation level Read Committed Snapshot Isolation – as long as you don’t have specified any other isolation level in your transactions.
With the NOLOCK query hint you temporarily change the isolation level for the specific SQL statement to Read Uncommitted. Therefore the SQL statement will also not benefit from Read Committed Snapshot Isolation, because the statement is not running in the default isolation level of Read Committed. Makes sense! Just think about this additional argument the next time when write magically … WITH (NOLOCK)…
Running every possible query with the query hint NOLOCK doesn’t really make sense. On one hand you can get back inconsistent data through Dirty Reads. And on the other hand you can’t benefit from the optimistic isolation level Read Committed Snapshot Isolation, because you have changed temporarily the default isolation level of your SQL statement.
Thanks for your time,