November 12, 2019 at 5:17 pm
In any regular Select query joining a few tables like below, does using this hint WITH (NOLOCK) really make any big difference?
In our environment, such hint is placed after every single table in every join in every stored proc.
SELECT 1
FROM #GR X WITH(NOLOCK)
JOIN GT GT WITH (NOLOCK)
ON X.i_gid = GT.Child_gid
JOIN Bi_Pi_A BPA WITH (NOLOCK)
ON BPA.e_gid IN (100,200,300)
WHERE BPA.rs = 'Z')
Likes to play Chess
November 12, 2019 at 5:41 pm
Yes, it can retrieve partially updated records and if a page-split is happening it can return duplicate rows.
Best to only be used on data that is not being updated and that you don't care if the results are accurate or not.
If a table is midway through a transaction it can be used to see how many rows have been inserted so far, so it can be useful in some instances.
November 12, 2019 at 9:42 pm
In our environment, such hint is placed after every single table in every join in every stored proc.
SELECT 1
FROM #GR X WITH(NOLOCK)
JOIN GT GT WITH (NOLOCK)
ON X.i_gid = GT.Child_gid
JOIN Bi_Pi_A BPA WITH (NOLOCK)
ON BPA.e_gid IN (100,200,300)
WHERE BPA.rs = 'Z')
Let's, just for a minute, assume that the use of WITH(NOLOCK) was ok to do. It's stupid to mark every single table with the hint especially if you ever need to change that in the future (and, you will). Learn how to use SET TRANSACTION ISOLATION LEVEL instead.
Of course, that would be only for the very rare exceptions that Jonathon speaks of. Both the WITH hint and the SET option are usually bad ideas.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2019 at 9:52 pm
Other than some advanced troubleshooting cases like Jonathan says, there is no reason you should use NOLOCK in your code.
NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables. That's what allows it to see uncommitted data, duplicate data, bad data, etc.
It was a bad hack in the SQL Server 2000 days to deal with the fact that the database's pessimistic locking scheme meant that readers block writers and writers block readers. Modern database systems use some form of multi-version concurrency, such as SQL Server's read committed snapshot isolation level:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
November 12, 2019 at 10:22 pm
Other than some advanced troubleshooting cases like Jonathan says, there is no reason you should use NOLOCK in your code.
NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables. That's what allows it to see uncommitted data, duplicate data, bad data, etc.
It was a bad hack in the SQL Server 2000 days to deal with the fact that the database's pessimistic locking scheme meant that readers block writers and writers block readers. Modern database systems use some form of multi-version concurrency, such as SQL Server's read committed snapshot isolation level:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
Keep in mind that SI causes big overhead to implement it. Therefore, SI should not automatically be applied to every db. Appropriate use of WITH (NOLOCK) is better than just rotely turning on SI for all user dbs.
Of course not every table should automatically be coded WITH (NOLOCK). That's just crazy. But NOLOCK does reduce overhead and thus definitely has a place in coding.
It's actually worse to automatically use SI than NOLOCK -- unless you have gobs of I/O capacity and disk -- but it's still a very bad idea to use NOLOCK indiscriminately.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 13, 2019 at 4:14 am
I stand to be corrected here, but NOLOCK on a #temptable has no effect. The #temptable is local to your SPID, so there is nobody else reading or writing to the #temptable while you are using it.
November 13, 2019 at 1:51 pm
Let's, just for a minute, assume that the use of WITH(NOLOCK) was ok to do. It's stupid to mark every single table with the hint especially if you ever need to change that in the future (and, you will). Learn how to use SET TRANSACTION ISOLATION LEVEL instead.
Of course, that would be only for the very rare exceptions that Jonathon speaks of. Both the WITH hint and the SET option are usually bad ideas.
Please, please, please, please, listen to this. It's so important. It's going to save you tons and tons of work. Let's say you never see a problem from NOLOCK (and honestly, you may not see them, but I know your business is, they just don't report it, they get weird data and rerun the query, maybe two or three times, and never tell you, they think it's all magic any way). You're writing that stuff in every query and every join. What a waste of effort. All you have to do is set the isolation level to READ UNCOMMITTED and you don't have to do all that work.
However, I have a secondary reason for pushing this. When you do finally realize that, OMG, missing and duplicate data is actually causing problems, and, OMG, running the same query 2-3 times in a row causes all sorts of additional contention and overhead that we otherwise would not have, and OMG, do something about it NOW!!!! You won't have to open up all 8 gajillion lines of code. You go one place, change to READ COMMITTED (or better still READ COMMITTED SNAPSHOT, I'll leave you to look that one up), and you've fixed the problem, quickly and easily with minimal to zero code changes and deployments needed.
Please, take the time to learn about SNAPSHOT isolation and stop hurting your business with that silly NOLOCK crap.
"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
November 13, 2019 at 6:29 pm
I stand to be corrected here, but NOLOCK on a #temptable has no effect. The #temptable is local to your SPID, so there is nobody else reading or writing to the #temptable while you are using it.
Yes, but, OP said they use it on every single join to every single table. Also, depends if local or global temp (I know this one was local)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 13, 2019 at 6:34 pm
NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying
I don't think so. Where did you get that from? Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.
Btw, NOLOCK is applicable only to SELECT.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 13, 2019 at 7:36 pm
Thanks everyone for your input on that. And Thank you, Grant.
Likes to play Chess
November 13, 2019 at 9:28 pm
Chris Harshman wrote:NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying
I don't think so. Where did you get that from? Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.
Sorry for not being specific enough. But yes it does take out a "Sch-S" lock on the table. No it doesn't take out page or row level locks, I never said that it did.
November 13, 2019 at 9:54 pm
ScottPletcher wrote:Chris Harshman wrote:NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying
I don't think so. Where did you get that from? Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.
Sorry for not being specific enough. But yes it does take out a "Sch-S" lock on the table. No it doesn't take out page or row level locks, I never said that it did.
Actually the full quote made it seem clear that you were talking about row/page locks.
It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables. That's what allows it to see uncommitted data, duplicate data, bad data, etc.
It was a bad hack in the SQL Server 2000 days
Since a schema lock can't be ignored, only row/page locks can, I think there's a clear inference that you were referring to the latter type of locks, as uncommitted data, etc., also have nothing to do with schema-level locks. What am I missing?
The only thing that's really important is that people understand accurately what NOLOCK does save in terms of locking. It does prevent the normal row share locks that SQL otherwise would have to take. This does provide a performance benefit, although it can be a slight one; then again, it can also prevent some deadlock situations. NOLOCK is a legitimate option today, not just in SQL 2000. And there are very good reasons not to automatically put every db in snapshot mode.
Even Oracle has moved away from the days when they forced every schema and table to have the overhead of a snapshot-style approach (although they used different terms for it: their dbms effectively required every table to use a snapshot-type techinque, using the rollback segment (as they called it), which often caused failures because of long-running trans, etc.). Yes, they bragged that "readers didn't block writers" and vice versa, but you paid for that in much added I/O and other overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 14, 2019 at 2:13 pm
Snapshot isolation is not serializable. Although Oracle had (and has ?) a serializable isolation level, according to the ISO standard it was (is) not serializable. See :
Serializable vs. Snapshot Isolation Level
In the past I have build simple examples to show this. The example in the given link is so much more illustrative.
Ben
Who uses 'dirty reads' to spy on the progress of other connections. In the running scripts I add rows to a timing table. These can be read from another connection, but if the transaction is still open, 'dirty reads' are needed.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply