notifying applications on db INSERT

Question

Consider an application with two components, possibly running on separate machines:

• Producer - Inserts records into a database, but does little to no reading from the database. Multiple instances may be running concurrently.
• Consumer - Must be notified when a record is inserted into the database by an instance of component A. May also have multiple instances.

What is the best way to perform the notifications, assuming that producers will be inserting 10-100 records into the database per second at peak times? The database technology is currently MySQL, but this is not necessarily set in stone. I can see a few different ways:

• Use something like MySQL message queue to "push" INSERT notifications to subscribers (consumers). Producers would have no knowledge that this was occurring.
• Have producers interact with an intermediate layer that performs the INSERT, and pushes notifications to a message queue that consumers are subscribed to.
• Have consumers poll the database frequently to check for new additions (seems like a bad idea)
• etc.

As far as coupling is concerned: Is it a good idea to have a two relatively separate application components perform direct queries on a shared database, or should one component "own" the database while the other component indirectly interacts with the DB via calls to the owning component?

Answer 1

I like the second proposed solution (the intermediate layer), as it separates the notification from the database work, and could possibly be part of a two-phase commit XA transaction. If the consumers need the database content in addition to the notification, that can be accomplished via MySQL replication. This could also address the coupling question, as the consumer components could have read-only access to their replicated instances.

Using a messaging solution would also address any potential bottlenecks in the database-only solution, as it would separate the notification and storage into separate processes.

Depending on the language, you have a number of choices for the message distribution. If you're using Java, I'd actually recommend JGroups rather than JMS, as it's somewhat easier to configure.

If Java isn't your language of choice, Apache's Active MQ supports a number of languages for interfacing. Apache's Qpid is an AMQP implementation that also supports a number of languages (Java, C++, Python, Ruby, etc.)

Other messaging options could include XMPP, STOMP, or RestMS implementations.