title: Query from multiple threads on a database table
body: <p>I have a database table with thousands of entries. I have multiple worker threads which pick up one row at a time, does some work (takes roughly one second each). While picking up the row, each thread updates a flag on the database row (like a timestamp) so that the other threads do not pick it up. But the problem is that I end up in a scenario where multiple threads are picking up the same row.</p>
  
  <p>My general question is that what general design approach should I follow here to ensure that each thread picks up unique rows and does their task independently. </p>
  
  <p>Note : Multiple threads are running in parallel to hasten the processing of the database rows. So I would like to have a as small as possible critical segment or exclusive lock.</p>
  
  <p>Just to give some context, below is the stored proc which picks up the rows from the table after it has updated the flag on the row. Please note that the stored proc is not compilable as I have removed unnecessary portions from it. But generally that's the structure of it.</p>
  
  <p>The problem happens when multiple threads execute the stored proc in parallel. The change made by the update statement (note that the update is done after taking up a lock) in one thread is not visible to the other thread unless the transaction is committed. And as there is a SELECT statement (which takes around 50ms) between the UPDATE and the TRANSACTION COMMIT, on 20% cases the UPDATE statement in a thread picks up a row which has already been processed.</p>
  
  <p>I hope I am clear enough here.</p>
  
  <pre><code>USE ['mydatabase']
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[GetRequest] 
  AS
  BEGIN
  
      -- some variable declaration here
  
      BEGIN TRANSACTION
  
      -- check if there are blocking rows in the request table
      -- FM: Remove records that don't qualify for operation.
  
      -- delete operation on the table to remove rows we don't want to process
      delete FROM request where somecondition = 1
  
  
      -- Identify the requests to process
      DECLARE @TmpTableVar table(TmpRequestId int NULL);
  
      UPDATE TOP(1) request 
          WITH (ROWLOCK)
             SET Lock = DateAdd(mi, 5, GETDATE()) 
          OUTPUT INSERTED.ID INTO @TmpTableVar
            FROM request tur
           WHERE (Lock IS NULL OR GETDATE() &gt; Lock)   -- not locked or lock expired
             AND GETDATE() &gt; NextRetry -- next in the queue   
  
          IF(@@RowCount = 0)
          BEGIN
              ROLLBACK TRANSACTION
              RETURN
          END
  
      select @RequestID = TmpRequestId from @TmpTableVar
  
      -- Get details about the request that has been just updated
      SELECT      somerows
      FROM        request
      WHERE       somecondition = 1
  
  COMMIT TRANSACTION
  END
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/LuJ0K.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LuJ0K.png" alt="enter image description here"></a></p>