Needed: A Windows Service That Executes Jobs from a Job Queue in a DB; Wanted: Example Code

Question

Needed:

• A Windows Service That Executes Jobs from a Job Queue in a DB

Wanted:

• Example Code, Guidance, or Best Practices for this type of Application

---

Background:

• A user will click on an ashx link that will insert a row into the DB.
• I need my windows service to periodically poll for rows in this table, and it should execute a unit of work for each row.

---

Emphasis:

• This isn't completely new terrain for me.
  • EDIT: You can assume that I know how to create a Windows Service and basic data access.
• But I need to write this service from scratch.
• And I'd just like to know upfront what I need to consider.
• EDIT: I'm most worried about jobs that fail, contention for jobs, and keeping the service running.

Answer 1

Given that you are dealing with a database queue, you have a fair cut of the job already done for you due to the transactional nature of databases. Typical queue driven application has a loop that does:

while(1) {
 Start transction;
 Dequeue item from queue;
 process item;
 save new state of item;
 commit;
}

If processing crashes midway, the transaction rolls back and the item is processed on the next service start up.

But writing queues in a database is actually a lot trickier than you believe. If you deploy a naive approach, you'll find out that your enqueue and dequeue are blocking each other and the ashx page becomes unresponsive. Next you'll discover the dequeue vs. dequeue are deadlocking and your loop is constantly hitting error 1205. I strongly urge you to read this article Using Tables as Queues.

Your next challenge is going to be getting the pooling rate 'just right'. Too aggressive and your database will be burning hot from the pooling requests. Too lax and your queue will grow at rush hours and will drain too slowly. You should consider using an entirely different approach: use a SQL Server built-in QUEUE object and rely on the magic of the WAITFOR(RECEIVE) semantics. This allows for completely poll free self load tuning service behavior. Actually, there is more: you don't need a service to start with. See Asynchronous Procedures Execution for an explanation on what I'm talking about: launching processing asynchronously in SQL Server from a web service call, in a completely reliable manner. And finally, if the logic must be in C# process then you can leverage the External Activator, which allows the processing to be hosted in standalone processes as opposed to T-SQL procedures.

Answer 2

First you'll need to consider

1. How often to poll for
2. Does your service just stop and start or does it support pause and continue.
3. Concurrency. Services can increase the likelihood of a encountering a problem

Implementation

1. Use a System.Timers.Timer not a Threading.Timer
2. Maker sure you set the Timer.AutoReset to false. This will stop the reentrant problem.
3. Make sure to include execution time

Here's the basic framework of all those ideas. It includes a way to debug this which is a pain

        public partial class Service : ServiceBase{

        System.Timers.Timer timer;


        public Service()
        {

        timer = new System.Timers.Timer();
        //When autoreset is True there are reentrancy problme 
        timer.AutoReset = false;


        timer.Elapsed += new System.Timers.ElapsedEventHandler(DoStuff);
    }


     private void DoStuff(object sender, System.Timers.ElapsedEventArgs e)
     {

        Collection stuff = GetData();
        LastChecked = DateTime.Now;

        foreach (Object item in stuff)
        {
            try
                    {
                        item.Dosomthing()
                    }
                    catch (System.Exception ex)
            {
                this.EventLog.Source = "SomeService";
                this.EventLog.WriteEntry(ex.ToString());
                this.Stop();
        }


        TimeSpan ts = DateTime.Now.Subtract(LastChecked);
        TimeSpan MaxWaitTime = TimeSpan.FromMinutes(5);


        if (MaxWaitTime.Subtract(ts).CompareTo(TimeSpan.Zero) > -1)
            timer.Interval = MaxWaitTime.Subtract(ts).TotalMilliseconds;
        else
            timer.Interval = 1;

        timer.Start();





     }

        protected override void OnPause()
     {

         base.OnPause();
         this.timer.Stop();
     }

     protected override void OnContinue()
     {
         base.OnContinue();
         this.timer.Interval = 1;
         this.timer.Start();
     }

     protected override void OnStop()
     {

         base.OnStop();
         this.timer.Stop();
     }


     protected override void OnStart(string[] args)
     {
        foreach (string arg in args)
        {
            if (arg == "DEBUG_SERVICE")
                    DebugMode();

        }

         #if DEBUG
             DebugMode();
         #endif

         timer.Interval = 1;
         timer.Start();

        }

    private static void DebugMode()
    {

        Debugger.Break();
    }



 }

EDIT Fixed loop in Start()

EDIT Turns out Milliseconds is not the same as TotalMilliseconds

Answer 3

You may want to have a look at Quartz.Net to manage scheduling the jobs. Not sure if it will fit your particular situation, but it's worth a look.

Answer 4

Some things I can think of, based on your edit:

Re: job failure:

• Determine whether a job can be retried and do one of the following:
  • Move the row to an "error" table for logging / reporting later OR
  • Leave the row in the queue so that it will be reprocessed by the job service
  • You could add a column like WaitUntil or something similar to delay retrying the job after a failure

Re: contention:

• Add a timestamp column such as "JobStarted" or "Locked" to track when the job was started. This will prevent other threads (assuming your service is multithreaded) from trying to execute the job simultaneously.
• You'll need to have some cleanup process that goes through and clears stale jobs for re-processing (in the event the job service fails and your lock is never released).

Re: keeping the service running

• You can tell windows to restart a service if it fails.
• You can detect previous failure upon startup by keeping some kind of file open while the service is running and deleting it upon successful shutdown. If your service starts up and that file already exists, you know the service previously failed and can alert an operator or perform the necessary cleanup operations.

I'm really just poking around in the dark here. I'd strongly suggest prototyping the service and returning with any specific questions about the way it functions.