3

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?

petenelson
  • 460
  • 2
  • 15
  • This answer might help you: [http://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue](http://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue) – Brian Rogers Dec 30 '11 at 21:44
  • 1
    A more standard approach is to use a proper message queue (say msmq). I think your approach is called the ghetto queue. – keni Dec 30 '11 at 21:45
  • Ditto to @keni. [RabbitMQ](http://www.rabbitmq.com/devtools.html#dotnet-dev) is particularly easy to set up and use. – TrueWill Dec 30 '11 at 21:59

2 Answers2

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.

Benjamin Pollack
  • 27,594
  • 16
  • 81
  • 105
  • Thanks Benjamin - it's a good solution to solve this quickly without changing architecture too much. I am starting to question handling the queue myself since it got called a ghetto queue by keni ;-) – petenelson Dec 31 '11 at 10:44
  • To move away from our Ghetto Queue was just not right for us at this time - I explored all the other options but Benjamin's solution worked the best for us - so thanks for that! I also put in a machine name column so I can see which machine is processing an item in the queue so that I can scale it up with more machines if required – petenelson Jan 03 '12 at 16:04
0

While your problem may benefit from a database transaction, I'm not sure that you would gain much for the long running processes with the same items queuing up. For that scenario I would recommend finding a better primary key that is meaningful to the data being submitted so that the data that would impact that row would be applied sequentially by your queuing framework. I would also recommend looking into existing queuing frameworks like Microsoft Queuing or IBM's MQ (admittedly I'm not very well versed in queuing frameworks).

CodeMonkey1313
  • 15,717
  • 17
  • 76
  • 109