Tales of a DBA fed up with NOLOCK. Part 1
44February 16, 2015 by Kenneth Fisher
Nov 12, 2014
Dear Diary,
They asked for help again. Every now and again everything slows down and they see a lot of timeouts. They have tried rebooting the SQL instance and that fixes the problem but it’s only a temporary solution. Of course last time they asked for my help they never implemented my suggestions so I don’t hold out a lot of hope.
Nov 14, 2014
Dear Diary,
Great day! I found the problem! It even has a simple fix! (can you tell I’m excited?) Turns out there is a SP that runs hundreds of times a minute. Every now and again it’s getting compiled with a value that’s a bit skewed. It means the SP runs just a little bit slower but that makes all the difference. I suggested adding OPTIMIZE FOR UNKNOWN to the queries in the SP and it should fix the problem.
Nov 17, 2014
Dear Diary,
No word on the changes yet but I’m sure they will be done testing soon. It’s not like the changes can cause the data to be different. It shouldn’t be all that hard to test. I can’t wait. It will be awesome to not have to worry about this problem anymore.
Nov 28, 2014
Dear Diary,
Still no word on the code changes I suggested. Last time this happened they decided it was to much work to test and they wouldn’t bother.
Dec 18, 2014
Dear Diary,
(*#*&%^(#$(%)^#*@
I got a response back. And I quote “I tested it but didn’t get a performance improvement. Here are the changes we will put in.”
They ^#*$(% added NOLOCK to each of the table references.
Dec 19, 2014
Dear Diary,
Oh for the love of …. Not only did they add NOLOCK everywhere they already had SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of the SP. Good thing I’m going on vacation in the next few days and can cool off.
Jan 10, 2015
Dear Diary,
I just went back and looked and they are setting the isolation level to READ UNCOMMITTED at the beginning of 90% of the code in this @*#&$ database. I’m even finding NOLOCK on temporary tables! These people have absolutely no idea what they are doing.
Jan 20, 2015
Dear Diary,
This will fix them! From now on they won’t be able to create or alter any code with NOLOCK or UNCOMMITTED in them. The HAL 0001 was just for fun. But now, I give you .. The HAL 0002!
CREATE TRIGGER HAL0002
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE,
CREATE_FUNCTION, ALTER_FUNCTION,
CREATE_VIEW, ALTER_VIEW,
CREATE_TRIGGER, ALTER_TRIGGER
AS
IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)') != 'HAL0002'
BEGIN
IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') LIKE '%NOLOCK%'
BEGIN
PRINT 'No, you may not use NOLOCK. No, not even for that.'
ROLLBACK
END
IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') LIKE '%UNCOMMITTED%'
BEGIN
PRINT 'READ UNCOMMITTED is just as bad as NOLOCK. No, just no.'
ROLLBACK
END
END
GO
SQL Studies 
Super awesome post!
This means I can’t issue
SELECT SomeColumn
FROM SomeTable NOLOCK
Too bad: it was my favourite table alias 🙂
Only if you are putting it in a SP, view or trigger. 🙂 Ad-hoc queries aren’t affected.
Can I ask how you would advise managing locking/blocking issues in a highly concurrent environment where it has been determined that some dirty reads are acceptable and the vast majority of update ‘transactions’ are implicit and affect only single rows?
Well, first of all I should point out that my post was more meant in fun than anything I would expect to be implemented.
Actually I’m actually rather surprised that if your update transactions are single row that you have a big blocking problem to begin with. If you have determined after careful testing that setting your isolation level to READ UNCOMMITTED is safe in your environment then go for it. But you should be aware READ UNCOMMITTED will not just cause you to read dirty data, you also run the risk of duplicate or missing rows from previously committed data.
I did realise that you were having some fun 🙂 – thanks for the response though. I’m just curious, would you advise setting the isolation level at the db level over and above using nolock hints?
If you are going to set it at the DB level then don’t use nolock hints. One or the other. Same goes for setting it within your SP using SET ISOLATION LEVEL. You get no benefit from doing it at multiple levels and if you decide to change things it will be easier (imho) to deal with later. Nothing worse than changing a setting and nothing happens because you forgot you set it in multiple places.
I will say I’m not an expert in isolation levels and I can’t say what effect using READ UNCOMMITTED at a server level will have (above and beyond the obvious). You might try asking the question on stackoverflow.
I appreciate the thoughts Kenneth. Hope I didn’t spoil the joke too much 😉
Not at all. Actually you pointed out something that I should know but don’t particularly well. I’ll have to read up on it 🙂
The way most people use nolock is like turning up your car stereo when you hear a worrying sound from the car. Sure it will hide the sound but it won’t fix the underlying problem.
Yes! 100% Great analogy!
[…] 16, 2014 Dear Diary, A few weeks back I introduced HAL002 to a database managed by some annoying devs. They were not just putting NOLOCK on almost every […]
I remember about a decade of everyone saying NOLOCK was the way to go for most fast OLTP installations in the instances where dirty reads were OK, prior to ever seeing a 601 error LOL
Can’t say I remember that personally, but the more I read about NOLOCK the more aggravated I get by this one groups insistence that it has no bad effects for them and is a great way to speed everything up. Oddly not the group you used to work with. They give me a different set of headaches 🙂
Whatever you used to travel back in time from Dec 19, 2014 to Jan 10, 2014, I want it! =)
Fricken’ typos! 😀
BTW, I loved the messages in the trigger. Cheers!
I’ve been a DBA for a long time. Certainly NOLOCK should not be used by default. But it does have its proper place. For example, for local temp tables, I see no downside to using NOLOCK and thus strongly recommend its use there. Also, for static tables, it reduces SQL overhead as well, such as a table of U.S. state code and Canadian provinces.
I agree NOLOCK has it’s place. I’d be hard pressed to find something in SQL that doesn’t have a use somewhere. Steve Jones even noted a use for Auto Shrink here: https://voiceofthedba.wordpress.com/2014/03/11/t-sql-tuesday-52-arguments/. That being said the problem is over use. My particular “problem” group uses NOLOCK routinely without understanding the ramifications.
And while I agree there is no down side to using NOLOCK on a temp table, what’s the up side? Unless it’s a global temp table you are it’s only user so there is no concurrency, and no reason to use NOLOCK. Same type of thing for static tables. You aren’t going to be using anything but shared locks anyway so there is no real benefit that I can see. Again not that it hurts.
The real problem is not understanding what it does and when/where it’s safe. I use it all the time when I’m writing little one of test scripts and I want to interfere as little as possible with whatever else is going on on the instance.
Also remember that this was for fun. I wouldn’t recommend putting this into a production system by any means. It’s a good example of a DDL trigger, and it’s amusing, but that’s really about it.
The reason to use it on temp tables, as always, is to reduce locking overhead.
I’m always amazed at how fast this guy can put up a post but Aaron Bertrand just posted this: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/. Basically it does a comparison of NOLOCK vs no hint on temp tables based on our discussion here.
Also he and I were discussing it a bit on twitter and we would both love it if you have any reproducible workloads that show it improving performance? We both feel like we would be surprised to see it causing any appreciable improvement (can’t see any harm either though). But as he put it “I am not afraid to be proven wrong.”
Last I checked the detailing logging, SQL does still take shared row locks on local temp tables when reading them unless you specify NOLOCK.
As it does for any SELECT without NOLOCK.
True, but even with NOLOCK some locks are taken. The whole point of lock escalation is to keep SQL from taking large numbers of locks anyway. If you look at Aaron’s example, which admittedly is small, you will see that the locks with and without NOLOCK are pretty much the same.
There’s only a minor difference, agreed. At any rate, I’m not a “developer”, as he relates, but a long-time DBA, as I stated above. I understand NOLOCK is not a huge difference in tempdb; I might quibble with whether it could be “significant” or not, depending on the number of users and the sizes of the temp tables. Putting 10K or 35K rows in the temp table might make for a more interesting test. As to I/O, no, I would not expect NOLOCK to reduce I/O.
Oddly the word “developer” means different things to different people, as does “DBA”. I consider myself a DBA but I’m probably more on the “developer” side of things to most people. IE I don’t deal with DR, HA, or anything server related. At least not in the office (I do at home for fun). I do performance tuning, developer support, database level support. I don’t think it was meant as anything but a label.
I would agree with the 10-30k and I may try that later tonight. And again, when it comes right down to it I can’t see any harm really, I just have a hard time believing there will be a benefit. Always willing to be proven wrong though 🙂
Scott, I definitely meant developer = (the writer of the code), not developer = (! the dba).
So what do you say to the cases (I haven’t personally done the leg work so I’m trusting other people’s work here) where some database developers found the following scenario.
SP sets transaction isolation level appropriately in the beginning.
Does a select or two
Does some inserts updates and some deletes
Does a select or two again and now because of lock escalation the isolation level is now at row or table lock. It didn’t de-escalate for some reason.
I had a blocking issue (this was a long time ago). I came across an article describing this scenario. I suspected I had the same issue (now shame on me for the taking the steps without verifying I had the same issue first, but I was in a hurry and testing the solution was faster and not dangerous). I tried his solution of adding WITH(NOLOCK) to blocked resources, it worked, it worked beautifully. Not only did the problem go away, this was a stored procedure shipped as part of a product. This issue was happening at 100s of implementations. The WITH(NOLOCK) fixed every one of them. It has solved many others too.
So, this was in SQL 2K5. We’ve moved on. 2014 and Azure are all available now. Does anyone know if the lock escalation has changed in a way that would make this solution suddenly not matter anymore. I’m hesitant to quit using WITH(NOLOCK) (granted I do know when I need to read only committed data vs when I don’t so this is safe for me, maybe not everyone else). Too many times I’ve seen the isolation level set at the SP opening and blocking still occurs and is simply solved by adding a (NOLOCK) hint. I’m unaware of any performance problem or execution plan changes from this so to me it seems reasonable to use some white space with a WITH(NOLOCK) as a general access method.
Now I’m reading an article that has a new hint to use. How long before this becomes something that people just do because it solved a painful experience? How long before the use of this hint gets overused and people start bashing everyone for using it? I agree, we shouldn’t just apply fixes without knowing if we have the issue relevant to the fix, however once we find a pattern that consistently avoids reoccurrences of an issue, why is it a bad thing to follow that pattern?
Whats hard here is that there ARE cases where NOLOCK is the right thing to do. However, what you have to remember is the risks. You said you put NOLOCK and it solved a lock escalation issue. I’m not surprised at all. That’s one of the reasons so many people love to use it. It’s a band-aid. It provides a quick fix without having to deal with any of the underlying issues. However it also provides a risk. Over time it’s almost inevitable that you will get bad data. I couldn’t tell you how much or how often but you have to decide if it’s acceptable. If I’m working with banking data then no, absolutely not. If I’m working with inventory data and I’m doing a true up every few weeks anyway then maybe I don’t care.
It’s all a matter of risk. One the one side you can spend a few hours, or even days to get a fix that doesn’t require NOLOCK, or on the other you can spend a few seconds, put NOLOCK in and run the risk of untrustworthy data. Also remember that the more active the system the higher the risk of bad data. So if you are using NOLOCK to solve concurrency problems (as opposed to the lock escalation in your example) then you run a much higher risk.
In the end the various isolation levels (including READ UNCOMMITTED/NOLOCK) are there for a reason. You just need to understand them and the risks behind each one before changing them.
Yes, this can be a difficult situation. First thing is to review the indexes on the tables, with special attention to making sure that *the best* clustered index for that table has been specified. (Hint: the majority of times this is **not** an identity column.) Getting all the best indexes will resolve many of the locking / concurrency issues. After that, you sometimes have to break queries into multiple steps to reduce locking / improve concurrency. It can a time-consuming process. But you should only use NOLOCK if you *know* it’s acceptable for the specific situation.
@Jason,
As Kenneth has said there are cases where NOLOCK is the answer. The problem is that it is too often the only answer that traditional developers can see. Just as they over use/abuse cursors to deal with operations they can’t grasp how to do with sets they also use NOLOCk as their Band-Aid.
I also have noticed that traditional developers tend to treat SQL and RDBMS as trivial and they tend to believe that a once thru of SQL IN 24 HOURS is enough to award them SQL Guru status. I realize that I am generalizing developers when there are exceptions but you need look no further then the ‘No-SQL’ movement the developer community has pushed for some time now. While they are legitimate reasons for what N-SQL does too many of these devs want it so that they can use SQL without having to learn how to use SQL. This idea of working with data in sets instead of RUBAR (Row by Agonizing Row) mystifies most of them and so they push for a SQL free database.
Think of it this way. If the DB Architects and admins were pushing for an Object Oriented free versions of C+ do you think the C+ coders would look at us like we were crazy?
this is all based on personal experience working with developers and dealing with some very poorly written code that runs fine on a small DB but does not scale well.
Apart from the vague “there are cases where it would be useful” I have not found in this topic an actual case where NOLOCK is actually of benefit.
For the time being, I am left seeing NOLOCK as a way to get incoherent results faster.
If a concrete positive case cannot be made for NOLOCK then why not instead describe how to prevent lock escalations ?
One case where I use it on a regular basis is to check the progress of large loads. I can query the table using WITH (NOLOCK) and get an approximate count of rows in the table without causing any difficulties to the load itself.
It’s important to remember that virtually everything in SQL has a use somewhere for some fringe case. In fact I can’t think of any that don’t off the top of my head. The important part is to know what any given tool/setting is useful for before using it and not to just use any specific thing “just because”.
>> I have not found in this topic an actual case where NOLOCK is actually of benefit.
We use NOLOCK on reports available only to DBAs for the express purpose of examining tables that have very high Insert rates (in order not to interfere with the end users’ experience). That’s it, no other exceptions … except for 3rd party code, of course, and that is peppered with them … I know that because of the number of 601 Alerts that I get in my Inbox …
I tend to agree. They don’t really hurt on things like lookup tables that are modified very rarely but on the other hand I doubt they help much there either. I do use them on a regular basis for my own queries but only if I know I’m looking for approximate info.
The safe solution is to switch to READ_COMMITTED_SNAPSHOT ON for that db. Then SELECTs will never block modifications (DELETE/INSERT/UPDATE). There is overhead associated with this, and it could even be quite significant. But it will allow more read concurrency.
Transaction Snapshot Isolation can be the answer in many cases, grabbing a snapshot in time of READ_COMMITTED data to work with. Doesn’t lock anything up, and you can use it for queries, but if used in a process that later updates, you might have to have code check to make sure the data you’re updating has not been modified by another process in the meantime. There are ways built into SQL Server to do this. I’d have to look up the exact syntax to do this. But if all you are doing is generating a report or just showing queried data, you at least avoid grabbing partially updated data right in the middle of another process updating or inserting a set of data.
(You have to enable snapshot isolation at the server level — which doesn’t make that the universal default, just enables it.)
[…] I posted Tales of a DBA fed up with NOLOCK and I got a fair number of comments (at least for me). And don’t get me wrong, I love […]
[…] is certainly a lot to put us on the naughty list. I’ve ranted several times about nolock, nolock and nolock. We have so many problems with security that it prompted me to write a session on SQL […]
[…] while back as part of a T-SQL Tuesday post I created HAL0001, then later HAL0002 with a long-term goal of building up a database version of HAL9000. Well, at the rate I’m […]
[…] HAL0002 stopped you from using NOLOCK in code. […]
[…] fun scripts: HAL0001, HAL0002, HAL0003, and […]
Thank you forr sharing
[…] done more than a few rants in my time blogging. One more should be a piece of cake. Interestingly the day I read the invite we […]