Locking Issues With Consume Mode SELECT Queries on a Queue Table | Vantage - Locking Issues with Consume Mode SELECT Queries on a Queue Table - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantageā„¢

Special Locking Issues Raised by Consume Mode SELECT Requests

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.

    A LOCKING request modifier is allowed, but 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 when receiving 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 you must 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.
  • Place your consume mode SELECT requests as early in a Teradata session mode transaction as possible to avoid conflicts with other database resources.
  • 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 performing a SELECT AND CONSUME operation on a queue table, the system also performs a row collection operation for each delete or update operation on same queue table, which impacts performance.

  • 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 queue table. This ensures that 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, isolate any SELECT AND CONSUME request as the only request in a transaction.

  • Vantage 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
  • Avoid coding large numbers of DELETE and UPDATE operations on queue tables because of the negative effect on performance.
  • Restrict DELETE, MERGE, or UPDATE operations on queue tables to exceptional conditions, because of negative performance impact. The critical factor is not the number of such operations, but their frequency.

    Otherwise, because of the likely performance deficits that result, code DELETE, MERGE, and UPDATE operations sparingly.

    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.

  • Vantage 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.

    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.