We're attempting to run a .Net 2.0 C# windows service concurrently on two application servers for scalability reasons, but am running into data concurency issues. The services poll the request table for new requests every 30 secs and I'm wrestling with finding a good way to prevent them from grabbing the same request and duplicate processing. Each windows service batch process requests entered through a web application and stored in a Sql Server 2000 table. The requests have statuses representing their process stage (1 = New, 2 = In-Process, 3 = Complete, 4 = Error).
I've tried the following processing server ownership approach which hasn't worked: adding a column to the request table to store the app server machine name and setting it and the request status within an update stored procedure. The sproc contains a Top 1 predicate and new request status criteria select inside a loop whose iterator is set by a parameter so I can return multiple rows per pass. The rows are updated then the effected request id(s) are returned to the service in a separate select. The first method called in the .Net windows service executes the aforementioned stored proc, gathers the returned request ids into a collection which I then iterate and create new request entities from using per entity selects.
The logic then attempts to prevent concurrency by using an update stored proc with its row locking along with logic in the .Net windows service main processing method to compare the executing environment machine name against the one in the request entity attribute and only proceed if they match.
Is there a better approach to take? Boy I hope so!
Start Free Trial
