Risks of NOLOCK, part 2

Question

Post reply

Risks of NOLOCK, part 2

Hugo Kornelis

SSC Guru

Points: 64790
More actions
November 2, 2015 at 10:53 pm

#399267

Comments posted to this topic are about the item Risks of NOLOCK, part 2
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
November 2, 2015 at 10:55 pm

This was removed by the editor as SPAM
November 3, 2015 at 5:21 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

call.copse SSCoach Points: 18064 More actions · Answer 1

call.copse

SSCoach

Points: 18064

November 3, 2015 at 2:56 am

#1837294

Thanks Hugo! Nice question.

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 2

Carlo Romagnano

SSC-Insane

Points: 22682

November 3, 2015 at 3:31 am

#1837303

Excellent!

edwardwill SSCertifiable Points: 5856 More actions · Answer 3

edwardwill

SSCertifiable

Points: 5856

November 3, 2015 at 4:40 am

#1837312

One of the options is missing a checkbox

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

edwardwill (11/3/2015)
One of the options is missing a checkbox

Yes. The third checkbox looks like it should be split into two separate checkboxes.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Stewart "Arturius" Campbell (11/3/2015)
Ed Wagner (11/3/2015)
edwardwill (11/3/2015)
One of the options is missing a checkbox
Yes. The third checkbox looks like it should be split into two separate checkboxes.
You'll notice the second half of that line matches the line beneath it.

Ahhh. I didn't even think to compare it to the others. Nice catch.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 6

Very good question Hugo, but I'm still not sure that "Nolock CAN NOT cause the query to not return committed rows" is the wrong answer.

Could anybody clarify me this point better?

Thanks in advance,

Mike

below86 SSChampion Points: 11538 More actions · Answer 7

Getting closer, got 2 of 4 yesterday and 3 of 4 today. Thanks for the questions, learning something new everyday.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

Ed Wagner (11/3/2015)
Stewart "Arturius" Campbell (11/3/2015)
Ed Wagner (11/3/2015)
edwardwill (11/3/2015)
One of the options is missing a checkbox
Yes. The third checkbox looks like it should be split into two separate checkboxes.
You'll notice the second half of that line matches the line beneath it.
Ahhh. I didn't even think to compare it to the others. Nice catch.

It appears that I made a copy/paste error when submitting the question. I pasted the third option in the third input box, and then apparently pasted the fourth option there as well before pasting it in the fourth box.

Those who have seen the interface for submitting a QotD will understand this. The boxes don't have room to show the entire answer, and in this case the answers are so similar that I didn't notice it while scrolling through the boxes. My apologies.

Steve, if you see this - any chance that you can get this fixed? I don't think it will cause people to miss the question, but it does annoy me. 🙂

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

michal.lisinski (11/3/2015)
Very good question Hugo, but I'm still not sure that "Nolock CAN NOT cause the query to not return committed rows" is the wrong answer.
Could anybody clarify me this point better?

I hope that this is not due to the double negation. I was debating on how to phrase this. The alternative would have been "Nolock CAN NOT cause the query to skip committed rows", which I at this time actually prefer over what I submitted.

Anyway, the explanation is the same as for returning the same row twice. Connection 1 starts a scan and processes pages 1 to 1000. At that time, connection 2 makes a change that causes a page split on page 1500. SQL Server sees that page 500 is empty, so it decides to allocate the new page there, then moves half the rows from page 1500 to page 500. The scan from connection 1 continues; once it reaches page 1500 it will only see the rows that are left there after the page split, not the ones that have been moved. And since i has already passed page 500, it will not see them at any time during the rest of the scan.

I hope this helps!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

George Vobr SSChampion Points: 10010 More actions · Answer 10

Thank you Hugo, refined questions very well prepared. It gave me a hard time, but it helped me this article:

http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

Kim Crosser SSCommitted Points: 1763 More actions · Answer 11

Hi Hugo - I got all four of these (surprise, surprise)... 😎

Like any tool, you should understand what it does before you use it...

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

Thanx Hugo 4 those 2 very useful questions, NoLock=NoDoubt 😉

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant