Risks of NOLOCK, part 2

  • 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/

  • This was removed by the editor as SPAM

  • Thanks Hugo! Nice question.

  • Excellent!

  • One of the options is missing a checkbox

  • 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.

  • This was removed by the editor as SPAM

  • 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.

  • 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

  • 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.

  • 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/

  • 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/

  • 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/

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

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

  • 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