August 4, 2020 at 6:29 am
Is it good idea to add hint NOLOCK in Sales.SalesPerson for second example. Any references that tells adding is not beneficial
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
August 4, 2020 at 7:07 am
nolock should NOT be added anywhere.
you can read (from many others) this one https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere
August 4, 2020 at 8:10 am
Most articles I found were talking about 1 table use.
What if I'm joining on table 2 and using NOLOCK there? Is it good to use? any issue that i can refer too
August 4, 2020 at 10:22 am
in order to keep things ATOMic, nolock is ignored in INSERT/UPDATE/DELETE operations.
even if you added it, it would be ignored.
Lowell
August 4, 2020 at 11:37 am
Adding NOLOCK here, or almost anywhere, is a bad idea.
Look, if you really want to have dirty reads, change your connection string to use READ_UNCOMMITTED. That's the same as NOLOCK, but you don't have to modify your code.
Then, when you finally realize just how bad a problem dirty reads are, you only have one place to fix everything instead of needing to rewrite every piece of code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2020 at 1:37 pm
Any reference link I can refer too and make my point?
August 4, 2020 at 1:39 pm
Thats a bad trade off we live in
Isloation is read committed and developers would like to use NOLOCK everywhere. Just needing some references to make my point nolock in delete on join table 2 is bad idea.
Can you help pls
August 4, 2020 at 3:22 pm
Stuart Ainsworth on Pinal Dave
In short, this is no kind of "new magic" never heard of before. There are very well published issues with NOLOCK. Microsoft states, in the bloody documentation, that NOLOCK will be ignored in the FROM clause.
I don't know what else to give you here. NOLOCK has a limited purpose in certain, relatively rare, circumstances. When you just paint your code with NOLOCK, you're doing it wrong. And it's not just me who is saying it (oh, and btw, look at the signature, I'm not trying to argue from authority, but I do have a little bit of knowledge here). You've got people like Aaron Bertrand. I'm sure I can track down more if necessary, but I can't see it being necessary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2020 at 3:22 pm
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2020 at 3:23 pm
And another. Cleaning up tabs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2020 at 3:52 pm
So NOLOCK would take effect in the following example:
DELETE spqh
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
August 4, 2020 at 4:43 pm
NOLOCK is NOT a bad thing when used properly, i.e., dirty reads are acceptable for that query (or you know that dirty reads aren't going to recur). There has been a valid war against NOLOCK everywhere; there has been not so valid war against NOLOCK anywhere.
People need to be made aware that phantom reads and non-repeatable reads can occur with READ COMMITTED locking as well. Schema locks are also, IIRC, taken for ANY SELECT, not just those with NOLOCK.
Thus, the only real issue is dirty reads. So, again, if dirty reads would cause you a problem, DON'T use NOLOCK. If not, you can consider it.
NOLOCK is especially useful on extremely static data, such as historical data. I can't imagine any reason not to specify NOLOCK for data that's guaranteed to remain static. For example, we have a table of state abbrevs with name to do lookups. NOLOCK seems obvious there. Similarly, we have tables that are only modified once a week, so NOLOCK for those too.
Allocation order scans could cause issues to be more prevalent with NOLOCK. If you really must you can avoid that by temporarily modifying the cursor threshold setting (ref: https://www.sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans).
But back now to the original reason to use NOLOCK in the first place:
NOLOCK avoids taking locks, which IS more efficient than taking locks. That's kinda self-explanatory, really, right?
Thus, yes, use CAUTION and understand the risks of NOLOCK. But don't automatically refuse to use it or "overblow" the dangers.
One alternative, SNAPSHOT, has wonderfully less blocking. But the overhead of SNAPSHOT is quite high. For example, the potentially heavy use of tempdb and the certain 14 overhead bytes added to each table row. [Perhaps even worse, those bytes are removed when a table REBUILD is done -- meaning that if a rebuilt row is UPDATEd, the 14 bytes are added again, possibly causing a page split.]
SNAPSHOT also has a hidden downside. You see data as from the start time of the query, period. If someone runs a report at 9:03AM that takes 3 hrs, do they really not want to see any later data on the report? Sometimes the answer to that q is not as easy as you might think.
That said, I do have dbs with READ_COMMITTED_SNAPSHOT ON. SNAPSHOT isn't inherently bad, any more than NOLOCK is inherently bad.
As we know, overall database performance is complex and involves many interrelated things.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2020 at 4:49 pm
Grant Fritchey wrote:So NOLOCK would take effect in the following example:
DELETE spqh
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2020 at 4:55 pm
Jonathan AC Roberts wrote:Grant Fritchey wrote:So NOLOCK would take effect in the following example:
DELETE spqh
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?
Interesting. I don't see why MS would block NOLOCK on a non-modified table in an UPDATE or DELETE. I would think it would be irrelevant to the UPDATE mechanism whether other tables had NOLOCK or not. I, too, understood the MS docs to mean only the actual table being modified. I'll have to read up on this further.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2020 at 4:58 pm
Thus, the only real issue is dirty reads. So, again, if dirty reads would cause you a problem, DON'T use NOLOCK. If not, you can consider it.
But...
The biggest issue with dirty reads is how they work. Lots and lots of people think about the classic 'cat' or 'dog' example. "Well, we're updating 'cat' to 'dog' but while that update occurs, some people might see 'cat'"
If that was all a dirty read was, cool. No big deal. However, dirty reads can lead to missing rows and duplicate rows. Not simply 'cat' or 'dog', but literally variable amounts of data, some possibly duplicate, some possibly missing.
Now, how bad are dirty reads? A lot worse. A whole lot worse.
Add to the fact that most people who are painting their code with NOLOCK already have very bad code and very bad structures, both of which lead to lots of scans, the main place where dirty reads are going to miss, duplicate, or otherwise muck with entire rows of data, and suddenly the NOLOCK problem is much worse. Telling people who have horrific code and structure problems that there's a magic bullet that fixes everything is where we get companies that require NOLOCK in every query (and yeah, run across this one many times now). Education on NOLOCK must be brutally clear that, more often than not, it's being abused.
Are there uses for NOLOCK?
Yes, absolutely. However, like any query hint, they should be used rarely, with a lot of planning, knowledge, and testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply