Oracle Advanced Queuing (AQ) with Dead-Letter Queue — Full Description

What Oracle AQ is

Oracle Advanced Queuing (AQ) is a native, kernel-level messaging system embedded directly inside the Oracle Database engine.
It allows the database to manage units of work as messages, with the same transactional guarantees as SQL operations.

AQ is not middleware, not an external broker, and not an application library.
It is part of the Oracle database kernel.

What a message represents

A message represents an intention to process something.
It is a runtime entity, not a declared object.

Key characteristics:

• Messages are not declared like tables or variables
• Messages exist only after enqueue
• Messages are stored physically as rows in a queue table
• Each message carries:
• a payload (business data)
• AQ metadata (state, retry count, timestamps)

Separation of responsibilities

Oracle AQ separates concerns clearly:

• Business tables
  Store domain data (orders, transactions, records)
• AQ messages
  Represent processing tasks or events related to that data

A row existing in a table does not mean it was successfully processed.
Only successful message consumption confirms processing.

Message lifecycle

The lifecycle of a message is entirely engine-controlled:

1. Message is created at enqueue time
2. Message is stored in the queue table
3. Message is delivered to a consumer
4. Consumer processes it inside a transaction
5. Outcome is determined only by transaction state

AQ does not inspect application errors, SQLCODE, or exceptions.

Success vs failure semantics

AQ defines processing outcome strictly:

• COMMIT
• Message is considered successfully processed
• Message is permanently removed
• ROLLBACK / crash / uncommitted work
• Message is considered failed
• Message remains eligible for retry

This design ensures transactional truth.

Retry mechanism

AQ includes an engine-managed retry system:

• Each failure increments an internal retry counter
• Retries are automatic
• Applications do not manage retry state
• Consumers simply process or rollback

This avoids custom retry logic and inconsistent behavior.

Dead-Letter Queue (DLQ) concept

The Dead-Letter Queue is AQ’s fault-isolation mechanism.

Purpose:

• Prevent permanently failing (“poison”) messages from blocking processing
• Allow normal messages to continue
• Preserve failed messages for later analysis or reprocessing

In Oracle AQ:

• DLQ is implemented as an exception queue
• It is automatically created with the queue table
• It uses the same physical storage

When messages move to DLQ

A message is moved to the DLQ when:

• It fails repeatedly
• The retry count exceeds the configured maximum

At that point:

• The message is quarantined
• It is no longer delivered to normal consumers
• Processing continues uninterrupted

This happens entirely inside the database engine.

What DLQ contains

The DLQ stores:

• The original message payload
• AQ metadata (retry count, state, timestamps)

The DLQ does not automatically store:

• SQL error messages
• PL/SQL stack traces
• Business validation reasons

If error details are needed, they must be logged explicitly.

Relationship between business tables and AQ

AQ does not replace business tables.

Typical separation:

·      Business tables store domain data

·      AQ stores processing intent

A row existing in a table does not mean it was successfully processed — only a committed dequeue confirms processing success.

Concurrency behavior

AQ is designed for high concurrency:

• Many producers can enqueue simultaneously
• Many consumers can dequeue simultaneously
• AQ guarantees:
• Exclusive message delivery
• No duplicate consumption in single-consumer queues
• Correct transactional isolation

Consumers compete for messages; ownership is not tied to the producer.

Consumer execution model

Consumer loops:

• Do not correspond to number of inserted rows
• Simply attempt to fetch available messages
• Stop naturally when the queue is empty

An empty queue is a normal state, not an error condition.

Why DLQ is essential

Without DLQ semantics:

• One bad message can cause infinite retries
• Processing pipelines can stall
• Manual intervention becomes mandatory

With AQ DLQ:

• Failures are isolated
• Processing continues
• Failed work is preserved safely

This pattern is identical to enterprise messaging systems (JMS, MQ, Kafka), but implemented inside Oracle DB.

What AQ DLQ is NOT

AQ DLQ is not:

• A logging framework
• A substitute for audit tables
• A mechanism to store error messages
• A replacement for validation logic

It is a resilience and isolation feature, not an observability feature.

Architectural value

Oracle AQ with Dead-Letter Queue provides:

• Engine-level fault tolerance
• Automatic retries
• Poison-message isolation
• Transaction-safe message handling
• No external dependencies

Examples are attached.