Using NoLock makes Read Committed Snapshot Isolation revert to Read Committed
While trying to debug performance for an application the other day, I noticed that the expected behavior for the application was to use Read Committed Snapshot Isolation level, however when a query was run it seems that it is running in isolation level Read Committed. Why the change?
Testing the problem
I’m using the Stack Overflow database on SQL 2017 on my local PC with some really slow NAS storage. I’m checking the isolation level using Brent Ozar’s BlitzWho stored proc available for free on GitHub. This stored proc shows the transaction isolation level for running queries which makes this nice and simple.
I have turned on read committed snapshot isolation by running the following command:
And I can see that it’s working by running a really long running query on posts in one window and blitzwho in another window:
Not a very exciting example, but everything is working as expected and I’m seeing “Read Committed Snapshot Isolation”. I went back and looked at the query being run, and it turns out there was one small difference:
The server is exactly the same as it was before, it’s pulling back the same data, but when I run sp_blitzwho now I get “Read Committed” instead of “Read Committed Snapshot Isolation”. The change is that I’ve specified a NOLOCK hint on the query. By specifying NOLOCK, I have instructed SQL to just read whatever is there at the time that it reads it, so SQL decides not to create the snapshot of what is there. You can confirm this using the sys.dm_tran_active_snapshot_database_transactions dmv. When you run with NOLOCK, no records are created in the DMV, when you run without NOLOCK you get an entry. This kind of makes sense – why should SQL go and do extra work to snapshot the last committed data when you’re instructing it to read the raw data?
In the case of our application, it was a bit more complex due to the NOLOCK hint being hidden in a view. Because of that one nolock hint, the whole query was run without snapshot data. I need to investigate further on what the nolock hint on one table does to the rest of the tables you join on in a query.
Moral of the story – tell your developers to not use NOLOCK – it’s usually not doing what they expect it to do anyway.