Is there a standard pattern for scanning a job table executing some actions?
- by Howiecamp
(I realize that my title is poor. If after reading the question you have an improvement in mind, please either edit it or tell me and I'll change it.)
I have the relatively common scenario of a job table which has 1 row for some thing that needs to be done. For example, it could be a list of emails to be sent. The table looks something like this:
ID Completed TimeCompleted anything else...
---- --------- ------------- ----------------
1 No blabla
2 No blabla
3 Yes 01:04:22
...
I'm looking either for a standard practice/pattern (or code - C#/SQL Server preferred) for periodically "scanning" (I use the term "scanning" very loosely) this table, finding the not-completed items, doing the action and then marking them completed once done successfully.
In addition to the basic process for accomplishing the above, I'm considering the following requirements:
I'd like some means of "scaling linearly", e.g. running multiple "worker processes" simultaneously or threading or whatever. (Just a specific technical thought - I'm assuming that as a result of this requirement, I need some method of marking an item as "in progress" to avoid attempting the action multiple times.)
Each item in the table should only be executed once.
Some other thoughts:
I'm not particularly concerned with the implementation being done in the database (e.g. in T-SQL or PL/SQL code) vs. some external program code (e.g. a standalone executable or some action triggered by a web page) which is executed against the database
Whether the "doing the action" part is done synchronously or asynchronously is not something I'm considering as part of this question.
