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).
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