We have multiple app servers running against a single database. How do I ensure that each row in a queue table only get processed once?

We have about 7 app servers running .NET windows services that ping a single sql server 2005 queue table and fetch a fixed amount of records to process at fixed intervals. The amount of records to process and the amount of time between fetches are both configurable and are initially set to 100 and 30 seconds initially.

Currently, my queue table has an int status column which can be either "Ready, Processing, Complete, Error". The proc that fetches the records has a sql transaction with the following code inside the transaction:

1) Fetch x number of records into temp table where the status is "Ready". The select uses a holdlock hint

2) Update the status on 开发者_JAVA百科those records in the Queue table to "Processing"

The .NET services do some processing that may take seconds or even minutes per record. Another proc is called per record that simply updates the status to "Complete". The update proc has no transaction as I'm leaning on the implicit transaction as part of the update clause here.

I don't know the traffic exceptions for this but figure it will be under 10k records per day.

Is this the best way to handle this scenario? If so, are there any details that I've left out, such as a hint here or there?

Thanks! Dave

Use UPDLOCK with READPAST.

Related answer here on StackOverflow...