November 2, 2015 at 10:53 pm
Comments posted to this topic are about the item Risks of NOLOCK, part 2
November 2, 2015 at 10:55 pm
This was removed by the editor as SPAM
November 3, 2015 at 2:56 am
Thanks Hugo! Nice question.
November 3, 2015 at 3:31 am
Excellent!
November 3, 2015 at 4:40 am
One of the options is missing a checkbox
November 3, 2015 at 5:04 am
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.
November 3, 2015 at 5:21 am
This was removed by the editor as SPAM
November 3, 2015 at 6:40 am
Stewart "Arturius" Campbell (11/3/2015)
Ed Wagner (11/3/2015)
edwardwill (11/3/2015)
One of the options is missing a checkboxYes. 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.
November 3, 2015 at 7:04 am
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
November 3, 2015 at 7:18 am
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.
November 3, 2015 at 7:38 am
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 checkboxYes. 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. 🙂
November 3, 2015 at 7:42 am
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!
November 3, 2015 at 11:36 am
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/
November 3, 2015 at 11:49 am
Hi Hugo - I got all four of these (surprise, surprise)... 😎
Like any tool, you should understand what it does before you use it...
November 3, 2015 at 11:09 pm
Thanx Hugo 4 those 2 very useful questions, NoLock=NoDoubt 😉
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply