title: Using SQL Server as a DB queue with multiple clients
body: <p>Given a table that is acting as a queue, how can I best configure the table/queries so that multiple clients process from the queue concurrently?</p>
  
  <p>For example, the table below indicates a command that a worker must process. When the worker is done, it will set the processed value to true.</p>
  
  <pre><code>| ID | COMMAND | PROCESSED |
  |  1 | ...     | true      |
  |  2 | ...     | false     |
  |  3 | ...     | false     |
  </code></pre>
  
  <p>The clients might obtain one command to work on like so:</p>
  
  <pre><code>select top 1 COMMAND 
  from EXAMPLE_TABLE 
  with (UPDLOCK, ROWLOCK) 
  where PROCESSED=false;
  </code></pre>
  
  <p>However, if there are multiple workers, each tries to get the row with ID=2. Only the first will get the pessimistic lock, the rest will wait. Then one of them will get row 3, etc.</p>
  
  <p>What query/configuration would allow each worker client to get a different row each and work on them concurrently?</p>
  
  <p>EDIT:</p>
  
  <p>Several answers suggest variations on using the table itself to record an in-process state. I thought that this would not be possible within a single transaction. (i.e., what's the point of updating the state if no other worker will see it until the txn is committed?) Perhaps the suggestion is:</p>
  
  <pre><code># start transaction
  update to 'processing'
  # end transaction
  # start transaction
  process the command
  update to 'processed'
  # end transaction
  </code></pre>
  
  <p>Is this the way people usually approach this problem? It seems to me that the problem would be better handled by the DB, if possible.</p>