A Common Business Problem : Messages (AQ)

Purpose

A common business problem is that applications will place messages into tables. These are record that represent work to be done (for example, process an order). The technical problem we need to solve here is how do we make it so that the following goals are met :

• each message gets processed at most once
• each message gets processed at least once
• many message must be worked on simultaneously by many background process.

How to do this ?

How can we have many users working concurrently on these rows while at the same time making sure each message gets processed at least and at most one time :

• We cannot update just a record and mark it in process.

If we did that and did not commit, all of the other sessions trying to get a message would block on that row trying to update it (so the messages wouldn't be worked on simultaneously)

• if we update a record an mark it in process and commit it before actually processing it, the message won't be processed. If our process fails, the message is marked as processed (or in process). No one else would ever pick it up.

In short, this is a very sticky problem in a relational database. Fortunately, the database provides one for us (AQ).