No, not NOLOCK. You get the wrong query results, even when your query results aren’t supposed to be changing.
I’m talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm.
SET LOCK_TIMEOUT 1000 – if you run this before your query starts, SQL Server will wait patiently for X milliseconds before giving up and aborting your own query. This is useful when a lot of short queries are running all the time, and I don’t want to give up instantly – but I do want to give up if there’s a long-running query blocking me. If you pass in a value of 0 milliseconds, SQL Server gives up instantly. For example, in the below query, I’m trying to delete all of Jon Skeet’s posts, but I want to abandon my query if anyone has locks that stop me on either the Users or Posts tables, and those locks stop me for over 1 second:
|
1 2 3 4 5 6 |
SET LOCK_TIMEOUT 1000; DELETE p FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
If you use this technique, or the other techniques described in this post, your query will fail with an error if it was blocked by someone. Most of us (myself included) don’t do a great job of checking for errors and retrying our query gracefully, so we could probably all use an hour to dig into Erland Sommarskog’s series on error handling to learn how to retry automatically.
WITH (NOWAIT) – this is kinda like setting lock_timeout 0, but it’s a table-level hint like NOLOCK. This means it only affects a single table in your query. For example, in the below query, I’m trying to delete all of Jon Skeet’s posts, but I want to abandon my query if someone has a lock on the Users table – but I’m willing to wait forever for locks on the Posts table:
|
1 2 3 4 |
DELETE p FROM dbo.Users u WITH (NOWAIT) INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
SET DEADLOCK_PRIORITY LOW – this isn’t about blocking, but specifically deadlocks. When I set this at the beginning of my batch, I’m saying, “I’m willing to wait for blocking, but if a deadlock situation comes up, go ahead and kill my query, because I don’t mind trying it again.” You call it like we did LOCK_TIMEOUT:
|
1 2 3 4 5 6 |
SET DEADLOCK_PRIORITY LOW; DELETE p FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
WAIT_AT_LOW_PRIORITY – this one is just for index maintenance, and sadly, it doesn’t work for creates or drops. “Online” index rebuilds aren’t completely online: they need a brief schema modification lock to swap in the new copy of the index. This hint lets your completed index lurk patiently in the background while other queries finish, and then swap in later:
|
1 2 3 4 5 |
ALTER INDEX PK_Id ON dbo.Users REBUILD WITH (ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF )) ); |
If that last one is helpful, then your next step is to do similar blocking avoidance with DML operations by using Kendra Little’s post on which operations support online & resumable.
28 Comments. Leave new
I was always taught that Table Hints were a bad idea, that devs often used them as shortcuts for dealing with performance issues and so I avoided their use especially NOWAIT. I thought NOWAIT was hinting to “NOT WAIT” on any locks to be released but it is the opposite of what its name implies. It’s been years since I looked at NOWAIT but I could swear I read articles online saying that NOWAIT could lead to dirty data when used in Updates.
For the ONLINE option for Index Rebuilds,, this may no longer be the case but in previous version that was not available in all versions of SQL Server. I believe it was Enterprise and up only.
Since there appears to be no way to update a posted comment I’ll have to leave a follow-up reply. I looked into the Table hints thing and it was the table hint NOLOCK that I was thinking of (that could lead to dirty reads) and not NOWAIT. That said I still think NOWAIT is a poor choose of names for a hint that is saying I’m willing to wait forever.
That’s incorrect. Read the post again and try the demo queries in the post so you can see how it works.
I’m not sure what you’re saying is incorrect. I admitted I mixed up NOWAIT with NOLOCK and your post says nothing about NOLOCK so unless yo’re referring to my comment about no way to edit a comment I’m nit sure as to what it is I have incorrect that I did not admit to being incorrect about.
I see now. I did not try the code, just read your post and when you said
“I’m trying to delete all of Jon Skeet’s posts, but I want to abandon my query if someone has a lock on the Users table – but I’m willing to wait forever for locks on the Posts table”
I read that (because of the last part) to say that NOWAIT means I’m wiling to wait forever. I just looked up the definition of NOWAIT and I see it does as its name implies. That’s my bad.
I wish there was some way to edit posts so you don;t have a thread of replies like this.
Thank you for this information. It includes some useful related options that I was not aware of. They mostly apply to writes and I wouldn’t normally use them on the system I maintain because they will cause updates to fail sooner and our application doesn’t handle failures well. Still, they’re useful tools to be aware of.
However, I would like to explain my perspective on (nolock) hints because a lot of people have a perfectly reasonable, but mistaken, hatred of (nolock) hints, and I think this is because there is a huge difference between developing an application and the sort of queries that most SQL Developers are usually actually writing.
Our main application is an Electronic Health Record system that I can update some Stored Procedures for but do not have access to most of the application code for.
Therefore, most of the queries I write are (effectively) reports that have to run from Production because they need to include up-to-date information. However, if they were run again in five minutes time, I wouldn’t be surprised to have different results.
For this purpose, I want the query to run quickly, to produce accurate information, and to have a minimal impact on everything else running on the system.
I don’t know exactly how SQL Server handles locking of SELECT queries. I DO know that performance AND data integrity (kinda) are both improved when using (nolock) hints because I have seen the difference and it is sometimes dramatic, but I do not really know the technical reason for this.
My theory is that there are two things affecting the performance;
1. Even on a really fast system, it takes some amount of time to lock records that the system could be using for other things, even if those locks are only shared ones.
2. Our system does a LOT of reads but relatively few writes. Those reads tend to look at a lot of records and, because of the Table Joins, there are often using the same records as other queries.
As for the data integrity – the reason I say “kinda” is because a well written database based application should have 100% Data Integrity so long as (nolock) hints (Dirty Reads) are not used – Once they are, the Data Integrity can potentially be damaged. I believe that this is Brent’s main concern. By definition, “Dirty Reads” can return incorrect data.
However, I think that the term “Data Integrity” has a wider meaning than just “Is the Data Stored in the Database internally consistent ?”. I think that it includes situations where the user tried to update a record and that update got lost. Even though the data in the database may still have internal consistency, it does not reflect all of the changes that the user has made.
In the real world, we don’t have a well written database based application. Pretty much no-one does. The use of transactions is limited so the application may make one change in the database and then fail on the second change without rolling back the first one. The error checking is almost always flaky so the user often isn’t even aware that a failure happened, let alone how to fix it.
If one of our users tries to update a Patient’s Chart and someone else is looking at one of the related records with locking turned on, the Update can fail. In our system, they (usually) get an error message alerting them to this but there is no explicit “Try it again” option. Even if there was, the application is usually writing back the whole records, not just the changes made by this user… So, if the failure happened because another user was trying to update the patient’s email address while this user was updating their surname, the patient’s record might be set back to the old email address when this user retries the update. Or the update might not get repeated because the user thought it had worked the first time, or because the application crashed altogether and the surname change was lost.
Whether they should or not, SELECT statements can affect UPDATE statements on our system. The update is slowed by the select and, if the select is being blocked itself, this can cause a deadlock. I don’t like deadlocks. I don’t want either update to be aborted because of a deadlock. Since the select statement is often touching a lot of records, if I can prevent the select statement being a part of the problem, it usually makes the problem go away completely even if the select statement wasn’t the direct cause.
I cannot prevent all bad updates. However, (nolock) hints do prevent a lot of them because the select queries are much faster and because they doesn’t cause failed updates. Two users can still overwrite each other but this is less likely to happen because one update failed and was retried, or because the application crashed.
Brent and most other Database experts ignore this aspect of data integrity. For me, I have literally never been aware of a query producing bad data because of (nolock) hints. I am certain that it has happened on my queries occasionally, but this is the thing… In the type of systems that I work with, it is very rare for two people to be updating the same patient’s clinical data at the same time. Even if they did, the impact on the query would usually be the same as if the query were run one second earlier or later… Very little of our data is inter-related. If Mr Smith gets a vaccination, it doesn’t take that away from Mr Jones. If Mr Gomez is diagnosed with diabetes, it doesn’t remove their existing diagnosis of hypertension.
As such, the type of failures caused by (nolock) hints are pretty innocuous on our type of system. Yeah, we could generate a bill that says Mr Rodriguez still owes the amount that they have literally just paid, but we usually generate invoices overnight when they are unlikely to be being paid for this particular reason.
If we were running a banking system or a stock control system, things would be different. But we aren’t.
Heck, if I were a developer on the application itself, I would improve the error handling. But I am not.
(nolock) hints CAN cause problems but, for the type of queries that I suspect most “DBAs” write, their benefits hugely outweigh their drawbacks in a system like the one I manage. And, I suspect that most other database applications are closer to this model than to one that is more heavily intertwined.
“Banning” (nolock) hints takes away a tool that is hugely powerful if it is used properly. Instead, we should be teaching people how and when they should use that tool and when they should NOT use it (for example, don’t use it on a subquery that uses the table that is currently being updated).
Simon – if the data isn’t changing, then why do you need nolock?
So sql server doesnt waste time n resources putting record locks right?
Is that something you’ve measured and found to be a significant bottleneck for your system, so significant that you’re willing to deal with incorrect query results?
SSRS queries that get run during the day on a static data warehouse , that only gets updated overnight. Can a static data warehouse during the day still return incorrect results with no lock?
Why would you need to avoid locking if the data isn’t changing?
Brent I was rereading this including Simon’s comment and he seems to be saying that avoiding locks based on multiple select statements. So you asked, why avoid locking if the data isn’t changing and he is saying that select statements would be faster. So basically he is saying if the use of a table is say some static reference but it is used in queries, NOLOCK is appropriate. That is what I’m reading anyway. Thoughts? I know this is super after the fact to ask. Thanks.
Eric – if you suspect that NOLOCK is faster, your best bet is to investigate that assumption. There are indeed edge cases where it can help achieve unordered allocation unit scans, but those are beyond the scope of this blog post.
(And to be clear – they’d be utterly irrelevant for static reference data.)
Very helpful. Thanks, Brent!
Nice post Brent. Only one pointed out, nolock will be replaced by READUNCOMMITTED, right?
I just like the last name Ozar (NO LIE)
AWWW YEAH
So sql server doesn’t waste time n resources putting record locks when it reads the data for the reports.
on a static data warehouse
Is that something you’ve measured and found to be a significant bottleneck for your system, so significant that you’re willing to deal with incorrect query results?
I challenge you to show us one example of using nolock on a static database with incorrect results.
Great. I challenge you to prove that it’s something you’ve measured and found to be a significant bottleneck for your system.
Have a great day!
Mike – demo’s all written, blog post is done, screenshots are uploaded. Goes live Wednesday morning. When can I expect your part of the bargain?
There is always SNAPSHOT. And ROWLOCK often limits the impact of lots of one-row updates
Yes, but snapshot isolation requires changing the database settings first, and I have pretty passionate feelings about rowlock hints. If you know you’re only updating one row, and you make that clear to SQL Server by the T-SQL, then you shouldn’t need that hint. If you’re updating a variable number of rows, then sooner or later someone’s going to push a huge number of changed rows through, and you’re going to run into memory problems due to the amount of memory required to manage all those locks. (Seen servers go unresponsive due to that.)
If you have a static DWH ( only maintenance / load over night ), why not set the database to ReadOnly after all processes have finished?
If you have a static DWH, why are you worried about blocking?