Messaging systems are based on one concept-store and forward. This concept is implemented by two key technologies: queuing (storing messages) and propagation (moving messages between queues).

Messages often represent critical business events. Consequently, the queuing technology has to have the same functionality and operational characteristics as the infrastructure for mission critical applications:

• The creation and consumption of messages must handled with the highest level of integrity.
• Messages must be protected against system failures of various kinds, including disasters.
• Message content needs to be accessible both for operational management purposes and for business decisions. This retrieval must be flexible and efficient.

Transaction processing systems have the desired characteristics to meet the first two requirements. In fact, many TP monitors have included queuing subsystems. Database technology, with powerful query capability, can meet the third requirement in addition to the integrity and durability requirements. Conventional file system-based messaging systems do not fully meet these requirements, especially for mission critical environments. Because database technology matches up well with these requirements, a new generation of messaging products, based on database systems, is emerging.

There are two basic approaches:

• Create a queuing system as an application to the database, i.e., use the database "as is" for storage of messages.
• Build queuing into the database infra-structure.

The use of the database "as is" provides significant benefits:

• Transaction support without necessarily requiring the overhead of two-phase commit.
• Integrated storage management.
• Recovery management.
• Restart management including disaster recovery.
• Full type and query support.
• Retention of messages allowing automated tracking.
• "Message warehouse" support.

The drawback of this approach is that databases are not inherently designed to be message queuing systems. This puts fundamental scalability and functionality limitations on messaging systems

that use the database "as is."

The following list specifies the demands that a message queuing solution places on a database that cannot be handled without changing the database. It also describes how these requirements are addressed by Oracle Advanced Queuing (AQ), a part of the Oracle8^TM object relational database management system.

• Message consumers may need to be informed immediately about the arrival of a new message.

If an application requests a message and no message exists, the database will return immediately with a 'Not Found' condition. This forces the application to 'poll' the database/queue for arrival of new messages. Polling is not scalable and its inherent latency is often not acceptable. AQ allows applications to specify how long they are willing to wait. AQ informs waiting applications as soon as a new message becomes visible, e.g., posting is done at the end of the commit process of the transaction that created the message.

• From a set of messages, consumers may like to see the highest priority message that has not yet been selected for processing.

Messages are typically ordered by importance, e.g., priority and/or creation time. To retrieve messages according to the desired order, a SELECT ORDERED BY SQL statement has to be used. While a message is being processed, it must be locked to ensure transactional semantics. Since all consumers using this queue will typically get the rows in the same order, only one application at a time can be processing a message. To solve this, Oracle internally added the ability to skip those messages that are already being processed by other applications. This enables the concurrent consumption of messages from multiple applications.

• Statistics are required to provide information about the current status of the queue, e.g., the number of messages in a queue or the average response time of the currently active messages.

Statistics create "hot spots" that cannot be handled with the traditional locking technology. Oracle AQ creates a work-list of all message creations and consumptions in a transaction. After commit completion, the statistics are updated based on the information in the work-list. This is a typical escrow approach, which also deals with application and system failures.

• Message management requires modifications to the index technology.

Messages are frequently added and deleted. This tends to create deep index structures even for relatively small numbers of records. The index algorithms have been enhanced to properly handle this problem.

• Messages need to be propagated between queues.

Executing the propagation as part of the database server removes the need for process context switching, which would occur between the database server and propagator.

• It is necessary to keep track of the number of attempts to process a message-and the count has to be retained through application and database failures. This causes problems with standard database techniques, because the "increase failure count" must be committed even if the rest of the transaction is rolled back.

If a transaction starts with the consumption of a message, which is almost always the case, Oracle AQ creates an internal counter. If the transaction rolls back, the database is reset and the 'attempt' count is updated and committed independently. If the attempt count reaches the user-defined maximum value, the message is moved to an exception queue.

• Messages may need to be created even if a transaction is rolled back.

Applications may be unable to process messages, however, the application may need to create a message about this condition. Oracle AQ allows applications to create a message that is 'outside' of the current transaction and immediately visible. A following abort request will rollback all the changes, however, the message will still be delivered.

The various database enhancements listed above were specifically aimed at addressing the following two issues:

• Provide scaleability
• Overcome the limitation of the flat transaction model

This list will grow as the requirements of the queuing technology grow, e.g., enhancements to support publish/subscribe.

In conclusion, for mission critical applications, messaging systems based on database technology are superior to those based on file systems. However, database technology needs to be modified to deal with the performance, scalability and functionality challenges of messaging.