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 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

+1 I didn't know that! Thanks!

CResults 2010-03-19 00:39:24

Thanks, this is perfect! I have one interesting difference though which I don't think changes anything but am not sure. I didn't bring it up in my question so I didn't confuse the issue. Besides, it's something I can easily revert...

Dave 2010-03-19 15:32:14

Instead of a single queue table, I have a parent table with a history table that contains the status. As a result, I never do an update anywhere. Instead I just insert a new row into the history table. Then I use a view that crunches the main table with the history table and pulls the most recent history row into the resultset. In other words, this pushes the burdon on the view instead of the udpate.The view I read from uses the same hints in the article and is also nested in the same transaction. Does anyone think this is a step in the wrong direction or a non-issue?

Dave 2010-03-19 15:33:50

@Dave Obviously it's subject to all the same locking issues whether you update a status row or insert a child transaction row, but if you use the same techniques, you should be fine.

Cade Roux 2010-03-19 15:57:51

ansaurus

tags:

views:

answers:

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?

related questions