We have about 7 application servers running Windows .NET services that ping a single SQL Server 2005 queue table and receive a fixed number of records for processing at fixed intervals. The number of records to process and the amount of time between samples are configured and are initially set to 100 and 30 seconds.
Currently, my queue table has an int status column, which can be "Finish, Process, Finish, Error." The handler that retrieves the records has an sql transaction with the following code inside the transaction:
1) Extract x number of records in the temp table, where the status is "Ready". Selection uses a hint with a commit
2) Update the status in these entries in the "Queue" table to "Processing"
.NET services do some processing, which can take seconds or even minutes to write. Another proc is called for each record, which simply updates the status to Complete. The update process has no transaction, as I rely on an implicit transaction as part of the update offer here.
I do not know traffic exceptions for this, but the figure will be less than 10 thousand records per day.
Is this the best way to handle this scenario? If so, are there any details that I forgot, such as a hint here or there?
Thanks! Dave
source share