🔒 Locked

Job / Queue with Database storage

Started by bilsa Jan 20, 2010 at 12:13 PM 3 replies 1.5k views

Original Post

Jan 20, 2010 at 12:13 PM

Hi! I have a problem with an application that uses a database for storing the state of different arbitary jobs to run. The issue I'm having is that if a job of a specific type is running, no other job with the same type may start, and sometimes if the application is forcibly shutdown - the state of an ongoing job might not be updated. For example: I have 3 jobs in the database, "Job1" was started but due to the application beeing forcibly shutdown, it will never complete and the status will be kept at "Running" - which will not allow the "Job3" to start, since they both have the same job type "JobType1": Job1 | JobType1 | Running Job2 | JobType2 | Finished Job3 | JobType1 | Pending I'm thinking I could add something like a "Timeout" value, which says how long a job may be running until it is treated as "Closed" and if it is already running it is terminated, thus allowing "Job3" to begin. However, I would preffer that jobs are allowed to run any amount of time, until it is actually completed. So, can someone give me input on how this can be done, or at least point me in the right direction - I'm guessing there should be some relevant concept solving this kind of synchronization issue. Basically what I want is to have a database with jobs, and multiple applications consuming the jobs. Thank you.

evolutional

Jan 20, 2010 at 12:16 PM

Which DBMS are you using, Sql Server?

bilsa

Jan 20, 2010 at 12:41 PM

Hi,

I'm using MSSQL 2005.

evolutional

Jan 20, 2010 at 1:04 PM

Lately, I've been doing similar stuff with Sql Server Service broker, basically using the app to post broker messages which are picked up and used to track update status. As you say, if a job hasn't been updated in X minutes (you'd stamp the date of the last update), you can declare it failed or cancelled - If I was doing this, I'd have some form of agent job monitoring things and implement the timeout system you refer to.

Alternatively, you could have a view over the top of the table, or a derived column that implements the timeout logic. It'd read the last status and timestamp, compare it to getdate() (using datediff()) and then display a "derived" status flag.

Imagine:

create view dbo.vw_Test as select JobRef, case when JobStatus = 'RUNNING' and datediff(mi, JobUpdateDTS, getdate()) <= 5 then 'TIMEOUT' else JobStatus end as JobStatus from dbo.MyJobTable

This view would then return "TIMEOUT" for jobs that've not updated in 5 minutes.

bilsa

Jan 23, 2010 at 6:02 AM

Hi,

Thanks, I was thinking along the same lines, however I was also thinking about setting up a "scheduling service" via .NET remoting, and let clients connect and request for jobs to execute.

And as long as the clients are still connected, the job is still running. If a connection is lost, the client has a timeout of lets say 2 min to reconnect and inform the scheduler that it is still running, otherwise the scheduler service sets the job as timed out.

Of course there is still a problem if the scheduler service would crash or fail or loose connection to the database - however, as I figure it the service would be some kind of "critical service" in this setup, and if it dies it is up to the administrator to manually restore / fix the jobs.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.