Transactional queueing/dequeueing

Question

I need to queue events and tasks for external systems in a reliable/transactional way. Using things like MSMQ or ActiveMQ look very seductive, but the transactional part becomes complicated (MSDTC, etc).

We could use the database (SQL Server 2005+, Oracle 9+) and achieve easier transactional support, but the queuing part becomes uglier.

Neither route seems all that great and is filled with nasty gotchas and edge cases.

Can someone offer some practical guidance in this matter?

Think: E/C/A or a scheduled task engine that wakes up every so often and see if there are any scheduled tasks that need running at this time (i.e. next-run-date has passed, but expiration-date has not yet been reached).

Answer 1

our system has 60 computers, each running 12 tasks (threads) which need to "get next job". All in all, it comes to 50K "jobs" per day. do the math of how many transactions per minute and realize task time is variable, so it is possible to get multiple "pop" events at the exact same time.

We had our first version using MSMQ. conclusion: stay away. While it did do just fine with the load and synchronization issues, it had 2 problems. one annoying and one deal breaker.

Annoying: as Enterprise software, MSMQ has security needs that just make it one more thing to set up and fight with the customers network admin.

Deal breaker: then came the time we wanted to take the next job, but not using a simple pop but something like "get next BLUE job" or "get next YELLOW job". can't do it!

We went to plan B: Implemented our own Q with a single SQL 2005 table. could not be happier

I stressed test it with 200K messages per day, worked. We can make the "next" logic as complicated as we want.

the catch: you need to be very careful with the SQL that takes the next item. Since you want it to be fast and NON locking. there are 2 very important SQL hints we used based on some research. The magic goes something like this:

SELECT TOP 1 @Id = callid
FROM callqtbl WITH (READPAST, XLOCK)
where 1=1 ORDER BY xx,yy

Answer 2

I've seen MSMQ used transactionally and it didn't seem particularly complicated - a Transaction SCope wrapped the enqueue or dequeue calls along with database access and all was well as long as the queue was defined as transactional once it was created. I don't think this is true with ActiveMQ, which is a message broker, but MSMQ is installed locally on each endpoint machine so getting an item transactionally into the queue doesn't require a fancy distributed transaction.

You are probably already be aware of this, but on .NET there are a few lightweight libraries that provide some nice abstractions over MSMQ (and theoretically other transports as well)

nServiceBus : www.nservicebus.com

Mass Transit : http://code.google.com/p/masstransit/

Also, Oren Eini has an interesting if experimental file system based, transactional queue. The benefit of this library is that, unlike MSMQ, it can be deployed as a library and does not require the maintenance headache of deploying MSMQ.

You can read about that here: http://ayende.com/Blog/archive/2008/08/01/Rhino.Queues.Storage.Disk.aspx

Also, SQL Server 2005 does handle queuing fairly elegantly, using SQL Server Service Broker, but you'll need SQL Server installed at each endpoint, and I don't know if SSB crosses the firewall.

Finally, if you don't get the answer you're looking for here I highly recommend the nSErviceBus discussion forum. Udi Dahan answers these kinds of questions along with his small band of message oriented followers, and it is the best resource I have found so far to get my queue oriented questions answered quickly and competently. That forum is here: http://tech.groups.yahoo.com/group/nservicebus/