How should I implement a multi threaded queue from a database table?

Question

My process is as follows:

1. User logs into web app and this drops an entry into the UserQueue table
2. A Windows Service polls this table every x seconds and processes each item
3. Once processed the item is deleted from the UserQueue table

This all works well with sequential processing but I'm concerned that a long-running task could block the queue for all other users (and this would be an issue for the web app).

I considered the BlockingCollection in .NET to hold the items in memory and then process them but I cannot guarantee that a row from the UserQueue table won't get put into that collection more than once (due to the non-unique nature of BlockingCollection) unless I use a database flag (BeingProcessed = true for example). I'm not keen on a database flag because if my service was stopped for any reason it could leave unprocessed items in the table with the BeingProcessed = true.

Is there a more standard approach to this that I am missing or should I consider Quartz.net or similar?

Answer 1

The basic trick is to use a test-and-set with a date, rather than just a simple boolean. Here's how you do that.

Let's say that your UserQueue table is super simple. Something like this, at the moment:

create table UserQueue (id integer primary key, description varchar not null)

So far, so good. But we want to safely grab a task and do something with it.

To start, let's alter the schema slightly:

create table UserQueue (id integer primary key, description varchar not null,
                        dtLocked datetime null)

Now, we simply follow a straight-forward procedure:

1. Look for a job that we can claim via select * from UserQueue limit 1
2. Attempt to lock it, setting the timestamp to NOW() where it is currently null via e.g. update UserQueue set dtLocked = NOW() where id = @id and dtLocked is null
3. Only proceed if at least one row was updated.

Because we're now using a datetime for the lock, we can clean out dead tasks on a regular basis via simple update statement that deletes locks older than some amount of time—say, five minutes.

As a bonus, this design lets you safely process multiple tasks at once, so you can eliminate any chance of a user task blocking by simply firing up more threads.