Databases
StackOverflow2013
Postsdbo
POUsing SQL Server as a DB queue with multiple clients
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing SQL Server as a DB queue with multiple clients
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSynesso
    1. USSynesso
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. USDan P
      1. VTFavorite
    1. COduplicate ... ..
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. USMitch Wheat
    2. COplease point me to the original, as SO didn't recommend a dupe.
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. USSynesso
    3. COWhy go through all the trouble of re-creating all this functionality when it's already available to any Windows server in the form of Microsoft Message Queueing (MSMQ) ?? Use what's available - don't constantly re-invent the wheel !
      singulars
      1. POUsing SQL Server as a DB queue with multiple clients
      1. USmarc_s
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload