There is a table
| jobID | status | startTime | endTime | and a job, that is scheduled to run every X seconds, it will take the first undone job ID, put it in stored proc and run it. Stored proc takes 1-2 minutes to run.
The users can:
- Add jobs to the table described above
- Take them out of the schedule
I can think of at least 2 risks that need to be avoided:
- Start a job just after someone has requested to take the job with this very ID out of the schedule
- Stop the wrong job: job stopping request was placed when the 'requested-to-stop' job was about to be completed, and is being executed while the next job, the one that we want to complete, is already running.
Performace is not too much of an issue, but, for some reason, locking the whole
| jobID | status | startTime | endTime | table takes more than 30sec, and it times out.
Updating status column is the obvious solution, but, if the timings of status changing and job staring coincide, there will be a problem (I guess my problem is simuilar to multithreading problem).
What is the best way to solve it? I think is this must be a very common problem, but I cannot think of the right key words to google it. Many thanks in advace!