Edit Question - KnowledgeHub

Title Body

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? 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. | ID | COMMAND | PROCESSED | | 1 | ... | true | | 2 | ... | false | | 3 | ... | false | The clients might obtain one command to work on like so: select top 1 COMMAND from EXAMPLE_TABLE with (UPDLOCK, ROWLOCK) where PROCESSED=false; 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. What query/configuration would allow each worker client to get a different row each and work on them concurrently? EDIT: 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: # start transaction update to 'processing' # end transaction # start transaction process the command update to 'processed' # end transaction 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.

Tags (comma-separated) Cancel