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.
- A requestor issues a SELECT AND CONSUME TOP 1 statement against an unpopulated queue table.
- The request accesses the FIFO cache and finds no rows.
- 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.
- A requestor issues an INSERT operation on the queue table previously specified by the delayed transaction.
- 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.
- 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.
- 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.