Locking Issues With Consume Mode SELECT Queries on a Queue Table | Vantage - Locking Issues With Consume Mode SELECT Queries on a Queue Table - Advanced SQL Engine

Locking Issues With Consume Mode SELECT Queries on a Queue Table | Vantage - Locking Issues With Consume Mode SELECT Queries on a Queue Table - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-24

dita:mapPath

ykx1561500561173.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1142

lifecycle

Product Category

Teradata Vantage™

About Special Locking Issues Raised By Consume Mode SELECT Requests

Several locking issues apply to consume mode SELECT operations. This topic introduces the most important locking issues with SELECT requests made against queue tables.

The following bulleted list documents the more important locking issues for consume mode SELECT requests:

The default lock assignment for a consume mode SELECT operation against a queue table has WRITE severity at the rowhash level.
You cannot lower the severity of this lock assignment.
The default lock assignment for a consume mode SELECT operation against the target table of an INSERT … SELECT AND CONSUME operation has WRITE severity at the table level.
Although you can specify a LOCKING request modifier, it has no effect on the behavior of the SELECT AND CONSUME operation: the system still retrieves rows in the same order and the query enters a delay state when the table is empty.
The system does not grant a rowhash-level WRITE lock on a queue table for a consume mode SELECT operation unless there is at least one row in the table to be consumed. This is unlike all other SQL requests, where the system grants locks at the time the it receives the request.
As soon as a row is inserted into the subject queue table, the following things happen:
- The system grants the WRITE lock on the first row hash in the queue.
- Transaction processing resumes.
- Rows are consumed until the queue is empty.
You can include only one consume mode SELECT request in a multirequest transaction in Teradata session mode unless you place all such consume mode SELECT requests between explicit BEGIN TRANSACTION and END TRANSACTION requests.
For example, the following Teradata session mode multirequest transaction is valid:
```
    BEGIN TRANSACTION;
      SELECT AND CONSUME TOP 1 *
      FROM myqueue;

      SELECT AND CONSUME TOP 1 *
      FROM myqueue;

      SELECT AND CONSUME TOP 1 *
      FROM myqueue;
    END TRANSACTION;
```
The following consume mode SELECT requests are also valid in Teradata session mode, but each is an individual implicit transaction (see Transactions, Requests, and Statements).
```
    SELECT AND CONSUME TOP 1 col_1_qits, qsn
    FROM myqueue;

    SELECT AND CONSUME TOP 1 col_1_qits, USER, CURRENT_TIMESTAMP
    FROM myqueue;

    SELECT AND CONSUME TOP 1 *
    FROM myqueue;
```
There are no special considerations for specifying consume mode SELECT requests in ANSI session mode except to remember that you must terminate ANSI session mode transactions by submitting either a COMMIT request or a ROLLBACK request.
SELECT AND CONSUME requests are the only SELECT requests where it matters whether you terminate the transaction with a COMMIT request or a ROLLBACK request.

The following is a valid ANSI session mode example that specifies two consume mode SELECT requests.
```
    SELECT AND CONSUME TOP 1 *
    FROM myqueue;

    SELECT AND CONSUME TOP 1 *
    FROM myqueue;

    COMMIT;
```

Every request you submit in ANSI session mode is treated as part of the same transaction until you submit an explicit COMMIT or ROLLBACK request.

If you submit a ROLLBACK request, then all the work that was done in the current session from the time the transaction began is rolled back and the work is lost.

You should place your consume mode SELECT requests as early in a Teradata session mode transaction as possible to avoid conflicts with other database resources.
You should avoid the following practices when issuing SELECT AND CONSUME requests.
- Coding any SELECT AND CONSUME requests within explicit transactions.
- Coding large numbers of SELECT AND CONSUME requests within a transaction, especially if there are also DELETE and UPDATE operations made on the same queue table as SELECT AND CONSUME requests.
  When the system performs a SELECT AND CONSUME operation on a queue table, it then also performs a row collection operation each time it does a delete or update operation on that same queue table, which has a performance impact.
You should place any action taken based on consuming a row from a queue table in the same transaction as the consume mode SELECT operation on that same queue table. This ensures that both the row consumption and the action taken on that queue table are committed together, so no row or action for that queue table is lost.
If no action is to be taken, then you should isolate any SELECT AND CONSUME request as the only request in a transaction.
Teradata Database aborts any transaction in which the limit on the number of SELECT AND CONSUME requests, 2 210, is exceeded.
You cannot code any of the following statements that operate on the same queue table as a SELECT AND CONSUME statement within the same multistatement request:
- DELETE
- MERGE
- UPDATE
You should avoid coding large numbers of DELETE and UPDATE operations on queue tables because of the negative effect on performance.
You should restrict DELETE, MERGE, or UPDATE operations on queue tables to exceptional conditions because of the negative effect on performance. The critical factor is not how many such operations you code, but how frequently those operations are performed. You can ignore this admonition if, for example, you run an application that performs many DELETE, MERGE, or UPDATE operations only under rarely occurring, exceptional conditions.
Otherwise, because of the likely performance deficits that result, you should code DELETE, MERGE, and UPDATE operations only sparingly, and these should never be frequently performed operations.

The reason for this advisory is that UPDATE, MERGE, and DELETE operations on a queue table are more costly than the same operations performed against a non-queue table because each such operation forces a full-table scan in order to rebuild the FIFO cache on the affected PE.
Teradata Database uses the Teradata dynamic workload management software to manage all deferred requests (transactions in a delayed state) against a queue table. The Teradata dynamic workload software client application software does not need to be enabled to be used by the system to manage delayed consume mode requests.
You should optimize your respective use of the two features because a large number of deferred requests against a queue table can have a negative effect on the ability of the Teradata dynamic workload management software to manage not just delayed consume mode queries, but all queries, optimally.
The system returns a failure response to the requestor when more than 20 percent of the sessions on a PE are already in a delayed state. Assuming the number of sessions is set for the default value of 120, the threshold number of delayed state sessions is 24.
A SELECT AND CONSUME request can consume any rows inserted into a queue table within the same transaction.