November 1, 2015 at 1:53 am
Comments posted to this topic are about the item Risks of NOLOCK, part 1
November 1, 2015 at 1:59 am
This was removed by the editor as SPAM
November 1, 2015 at 9:41 am
Stewart "Arturius" Campbell (11/1/2015)
Good question, thanks HugoAnother side-effect: any records that are exclusively locked by another process may be skipped, thus returning an incomplete result set.
Thanks, Stewart!
Your remark is incorrect, though. NOLOCK will ignore locks, so rows that are exclusively locked will still be returned. My guess is that you are confusing NOLOCK with READPAST - that hint does have the effect of skipping rows that have incompatible locks.
However, there are a lot of other side effects. That's why this is part 1. If I am not mistaken, part 2 is scheduled for tomorrow, so you might want to start thinking about all those other side effects already... 😉
November 1, 2015 at 11:30 am
I get it wrong because I forgot this from BOL:
READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.
November 1, 2015 at 10:29 pm
This was removed by the editor as SPAM
November 2, 2015 at 5:10 am
Good question, Hugo. The constraints got me. I'm looking forward to part 2 tomorrow.
November 2, 2015 at 6:09 am
Nice question Hugo, cheers.
Got 3 out of 4, think it was the important 3 though!
November 2, 2015 at 6:26 am
Gazareth (11/2/2015)
Nice question Hugo, cheers.Got 3 out of 4, think it was the important 3 though!
Yeah, I was in the same boat. Was it the constraints for you, too?
November 2, 2015 at 6:53 am
Great question, Hugo! Do you have any measurements on just how much READUNCOMMITTED (I don't like the synonym NOLOCK since it is an oxymoron -- that is, schema locks are always taken, IIRC) reduces processing time by not having to check for other locks or request them? If not, I may cook up some tests to see if I can measure it.
Gerald Britton, Pluralsight courses
November 2, 2015 at 7:17 am
Hugo,
Great question. I did not get "Nolock CAN cause the query to return data that violates constraints". I thought that constraints are checked first before any insert/update etc. Could you explain?
November 2, 2015 at 7:33 am
g.britton (11/2/2015)
Great question, Hugo! Do you have any measurements on just how much READUNCOMMITTED (I don't like the synonym NOLOCK since it is an oxymoron -- that is, schema locks are always taken, IIRC) reduces processing time by not having to check for other locks or request them? If not, I may cook up some tests to see if I can measure it.
I don't have hard numbers. Throwing out a wild ballpark figure, I'd expect it to be probably somewhere in the one to two percent area for simple queries that can fetch data from cache. If data has to be read from disk, or if a lot of complex stuff is going on in the query, then the time spent on locking should remain about the same while the rest takes more time, so the percentual saving would drop even lower.
But please do test this and share your results!
November 2, 2015 at 7:39 am
Pei Zhu-415513 (11/2/2015)
I did not get "Nolock CAN cause the query to return data that violates constraints". I thought that constraints are checked first before any insert/update etc. Could you explain?
I guess you just explained this yourself, in the "I thought that ..." part. That is where you were wrong.
All execution plans of insert, update, and delete statements I have ever seen will always first do the modification, then check the constraints. SQL Server will sometimes decide to update (some of) the nonclustered indexes on the table after checking constraints, but at least the clustered index (or heap) always gets update first. And that creates a small window of time in which a query with NOLOCK can get to see the unverified data.
Note that only CHECK constraints and FOREIGN KEY constraints are ever explicitly checked. For UNIQUE and PRIMARY KEY constraints, the check is implicitly done at the moment the supporting index gets updated. So if the clustered index supports a PRIMARY KEy or UNIQUE constraint (and with defaults, that is usually the case), then that constrain will be checked at the time the clustered index gets updated, which is the first step in the plan. That is the only constraint that you can never see violated even with read uncommitted.
November 2, 2015 at 7:50 am
A Select query with NOLOCK could return uncommitted results. That can include rows about to be deleted or updated. If those rows include FKs e.g. pointing to tables that have just been committed(e.g. by deleting the referenced rows), the NOLOCK query can return rows violating those FKs.
Gerald Britton, Pluralsight courses
November 2, 2015 at 7:57 am
I got this one wrong because I reckoned that when there is no concurrent activity the saving from not requesting shared locks is so small as to be absolutely negligible, so I answered that NOLOCK in the absence of concurrent activity had no effect on performance :blush:. Although if sufficient things are accessed to make requesting and releasing shared locks a significant overhead SQL Server will upgrade to use locks with a higher grain, to push the cost back down, it's still true that the cost is non-zero, so Hugo's answer is correct and I should have said that it may reduce cost.
Perhaps I was prejudiced by two things: having worked with hardware where getting a shared lock (not in SQL Server, which didn't exist at the time) in the absence of conflict cost 2 microcycles and releasing it had the same cost; and I know that performance can be affected by random environmental variations (maybe modern computers are immune to the sort of thing we used to observe in the bad old days, but things like different EM noise levels at different times of day affecting the number of IO retries and/or the number of error correction cycles on RAM did happen and were measurable, and I was told by some engineers that small temperature fluctuations in the server room could affect both mill speed and signal propagation rates, albeit very slightly indeed since temperature tended to be pretty tightly controlled - but I suspect that hasn't changed since getting rid of such effects probably requires changes to the laws of physics).
Anyway, I didn't use NOLOCK even in "safe" cases because it's impossible to rule out that some idiot will start running updates when he's not supposed to and that may cause troubles - and claims that errors in a report will be unimportant are nonsense, based on the idea that only a few values will be wrong (because roll back is rare), it's quite possible that something as simple as a row count may have a large error if there are page splits, and if one gets a lot of rows with a low value duplicated while a lot of rows with a high value are omitted most other aggregates (AVG, VAR, VARP, STDEV, STDEVP, SUM) returned for that value may be very wrong. Maybe in an environment where the DBAs are really disciplined it wouldn't happen, no-one would run any updates or inserts when they shouldn't, but my experience is that DBAs (and SysAdmins too) tend to believe that minor things like operational directives don't apply to them. 😉
Tom
November 2, 2015 at 8:01 am
Thanks Hugo. I had the impression that all constraints are checked before any updates. Now I recalled in Itzik Ben-Gan's book that it mentioned the order of execution.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply