c# - 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.