title: Why Does SQL Update Top apparently reduce locking even when no records are updated?
body: <p>I have a question about why some SQL (running on SQL Server 2005) is behaving the way it is.  Specifically, I made a change to reduce lock contention during an update and it seems to be working in cases I didn't think it would.</p>
  
  <p><em>Original code:</em></p>
  
  <p>We had an update statement like this, that was being applied to a table with more than 3,000,000 records:</p>
  
  <pre><code>UPDATE  USER WITH (ROWLOCK)
  SET Foo = 'N', Bar = getDate()
  WHERE ISNULL(email, '') = ''
  AND   Foo = 'Y'
  </code></pre>
  
  <p>As you can probably guess, this seemed to lock up the USER table for a while.  Even with the ROWLOCK hint, other jobs running queries and updates against USER would block until this was done.  That's not acceptable for this particular application, so I thought I'd apply a trick I read about by having the update statement only update 100 records at a time.  This would give other queries a chance to get at the table occasionally.</p>
  
  <p><em>Improved code:</em></p>
  
  <pre><code>DECLARE @LOOPAGAIN AS BIT;
  SET @LOOPAGAIN = 1;
  
  WHILE @LOOPAGAIN = 1
    BEGIN
      UPDATE TOP (100) USER WITH (ROWLOCK)
      SET Foo = 'N', Bar = getDate()
      WHERE ISNULL(email, '') = ''
      AND   Foo = 'Y'
  
      IF @@ROWCOUNT &gt; 0
        SET @LOOPAGAIN = 1
      ELSE
        SET @LOOPAGAIN = 0
    END
  </code></pre>
  
  <p>This did the trick.  Our update did its work and other queries were able to get at the table.  All is happiness and light.</p>
  
  <p><em>The Mystery:</em></p>
  
  <p>I understand how this improved the performance when there were many records in the table it had to update.  By taking a quick run through the loop after every 100 updates, it gave other queries a chance to get at the table.  The mystery is that this loop had the same effect even when there were no records affected by the update!</p>
  
  <p>The second time we would run our original query, it would run for only a fraction of the time (say 30 seconds or so), but it would lock up the table during that time even though no records were being changed.    But put the query in the loop with the "TOP (100)" clause, and even though it took just as long to do nothing, it freed up the table for other queries!</p>
  
  <p>I'm very surprised at this.  Can anyone tell me:</p>
  
  <ol>
  <li>If what I just said is at all clear and,</li>
  <li>Why the second block of code allows other queries to get at the table even when there are no records being updated?</li>
  </ol>