15.00 - Consume Mode Transactions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Consume Mode Transactions

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 look something like the following.

1 A requestor issues a SELECT AND CONSUME TOP 1 request 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 request 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 request 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 requests 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 request 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 SQL Data Manipulation Language for further information about how the INSERT, SELECT, and SELECT AND CONSUME requests process event processing‑related data.

    See SQL Request and Transaction Processing for further information about management of queue table transactions by Teradata Database.