Consume Mode Transactions | CREATE TABLE (Queue Table Form) | Teradata Vantage - Consume Mode Transactions - Analytics Database - Teradata Vantage

A consume mode transaction enters a delayed, or waiting, select state whenever a SELECT AND CONSUME operation fails to find rows in the specified queue table. An INSERT operation to that queue table wakes up the transaction.

When you retrieve a row in consume mode, the system then deletes it from the table.

A typical consume mode process might include the following.

1. A requestor issues a SELECT AND CONSUME TOP 1 statement against an unpopulated queue table.
2. The request accesses the FIFO cache and finds no rows.
3. The system puts the transaction containing the unfulfilled request into a delayed state until the status of the queue table changes.

   No more than 24 transactions per PE can concurrently be in a delayed state. When this number is exceeded, the system returns an error to the requestor.

4. A requestor issues an INSERT operation on the queue table previously specified by the delayed transaction.
5. The act of inserting a row set into the queue table sends a message to the delayed transaction to awaken it. Delayed transactions can also be awakened in either of the following ways.
   • Through direct intervention by issuing an ABORT statement against the transaction
   • Through indirect intervention by dropping the queue table while a delayed transaction is pending against it.
6. The awakened transaction performs a SELECT AND CONSUME TOP 1 operation against the newly populated queue table.

   Note that a SELECT AND CONSUME statement can consume any rows inserted into a queue table within the same transaction.

7. After the select request has been satisfied, the system consumes (deletes) the selected row set from the queue table.

You should position your SELECT AND CONSUME TOP 1 statements as early as possible within a transaction to avoid conflicts with other database resources. This is to minimize the likelihood of a situation where the SELECT AND CONSUME TOP 1 statement would enter a delayed state while holding locks on resources that might be needed by other requests.

The only lock assignment available for a SELECT AND CONSUME TOP 1 operation is a severity of WRITE applied at the row hash level.

See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for further information about how the INSERT, SELECT, and SELECT AND CONSUME statements process event processing-related data.

See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for further information about management of queue table transactions by the database.