16.10 - Locking Issues With Tactical Queries - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

Special Locking Issues Raised by Tactical Queries

Tactical queries deliver better response times and throughput using rowhash-level locks whenever possible. Rowhash-level locks are preferable for the following reasons:

  • Higher concurrency

    If a lock is placed on only one or few rows, the other rows in the table can be accessed or updated by other users at the same time.

  • Fewer resources required

    Rowhash-level locks require less resources to apply because only a single AMP is engaged. When you apply table-level locks, work must be performed and coordinated across all AMPs in the system. Maintenance of table-level locks always requires two separate all-AMPs steps: the first to set the lock and the second to release it.

  • Greater scalability

    When the fewest resources are marshaled to satisfy a request, then a greater number of similar requests can be processed in parallel. Throughput increases both as the number of concurrent users increases and as more nodes are added to the configuration.

See Database Design for information about how to design your databases in ways that facilitate mixed tactical and decision support query workloads.

Group AMP Locking Considerations for Tactical Queries

Group AMP operations use a series of rowhash-level locks, one for each of the rows touched by the query. See Database Design for more information about group AMP operations.

One of the major benefits of group AMP processing is that it significantly reduces the need to apply table-level locks. Table-level locking, because it is at a higher level than rowhash-level locking, exposes database objects to greater contention, so it is more likely to slow, or even block, other concurrently running operations. The Optimizer also applies table-level locks in a separate step, and all AMPs in the system are included in the step that gets and applies table-level locks.

In contrast to these operations with a negative impact on performance, group AMP operations can improve conditions for both read and update concurrency because the locks they place are not only set at lower levels, but also in fewer places. The larger the number of AMPs in the configuration, the greater the performance benefit obtained from group AMP operations, and the more likely the Optimizer is to specify group AMP-based query steps.

ACCESS Locks and Tactical Queries

ACCESS locks provide greater throughput if updating by one group of requests and reading by another is occurring on the same tables. ACCESS locks permit you to have read access to an object that might already be WRITE- or READ-locked.



When you use ACCESS locks, there is a risk that the view of the data being accessed is inconsistent. Data in the process of being updated might be returned to a requestor as if it were consistent. In the case of an ACCESS locked read operation on a geospatial index column, Teradata Database returns a retryable error to the requestor if the current state of the Hilbert R-tree does not permit all unmodified rows to be returned. If this is not acceptable for an application, then you should not use ACCESS locks.

Rowhash-Level or Table-Level ACCESS Locks for Tactical Queries

ACCESS locks can reduce wait times for queries, but they can also add unnecessary work if they are not handled carefully. You must understand the granularity of the lock and the nature of the query to ensure not to add unnecessary overhead to the workload.

For example, the modifier LOCKING TABLE customer FOR ACCESS requests a table-level lock and results in an all-AMPs operation even if there is only a single-AMP step in the query plan. Using this locking modifier can add two additional, unnecessary all-AMPs steps to the query plan and forces extra Dispatcher steps to be sent between the PE and the AMP.

In the following EXPLAIN report, note the separate step, Step 1, generated to perform the all-AMPs table-level lock, and the additional step, Step 3, that releases the table level lock across all AMPs.

     EXPLAIN
     LOCKING TABLE customer FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_custkey = 93522;
Explanation
-----------------------------------------------------------------------
  1) First,  we lock CAB.customer for access.
  2) Next, we do a single-AMP RETRIEVE step from CAB.customer by
     way of the unique primary index "CAB.customer.C_CUSTKEY =
     93522" with no residual conditions. The estimated time for this
     step is 0.03 seconds.
  3) Finally,  we send out an END TRANSACTION step  to all AMPs involved
     in processing the request.

For a single-AMP operation such as reading a single row using a primary index value, LOCKING ROW FOR ACCESS is always a better locking modifier to use than locking the entire table. You can see in the EXPLAIN report that a row-level ACCESS lock is applied and that only one AMP is used to process the query.

The following EXPLAIN text illustrates a row-level ACCESS lock.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_custkey = 93522;
 
Explanation
-----------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from CAB.customer by
     way of the unique primary index "CAB.customer.C_CUSTKEY =
     93522" with no residual conditions  locking row for access. The
     estimated time for this step is 0.03 seconds.

Escalating Lock Enhancements Automatically

Even if you explicitly specify a rowhash-level ACCESS lock, the Optimizer automatically converts it to a table-level lock if the query plan requires an all-AMPs operation and there is only one table referenced in the query. Always check the EXPLAIN report to verify that row hash or, when appropriate, table-level ACCESS locks are issued for tactical queries.

The following EXPLAIN report for an all-AMPs query shows that the Optimizer applies a table-level ACCESS lock even though the LOCKING request modifier explicitly requests a rowhash-level ACCESS lock.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_nationkey = 15;
Explanation
-----------------------------------------------------------------------
1) First,  we lock CAB.customer for access.
2) Next, we do  an all-AMPs RETRIEVE step  from CAB.customer by way
   of an all-rows scan with a condition of
   ("CAB.customer.C_NATIONKEY = 15")
   into Spool 1, which is built locally on the AMPs. The input table
   will not be cached in memory, but it is eligible for synchronized
   scanning. The size of Spool 1 is estimated with high confidence
   to be 300,092 rows. The estimated time for this step is 2 
   minutes and 8 seconds.
3) Finally,  we send out an END TRANSACTION step  to all AMPs
   involved in processing the request.

Even if more than one table is specified in the request, if a LOCKING ROW FOR ACCESS modifier has been specified, the Optimizer applies a table-level ACCESS lock for all tables undergoing all-AMP access in that request.

The following graph illustrates the cost of using table-level ACCESS locks compared to rowhash-level ACCESS locks when the request itself only performs single-AMP operations.



The elapsed time represents the total time to perform 100 000 single-row SELECT requests using 20 sessions. When table-level ACCESS locks were specified for the request, total execution time for this workload increased by a factor of 5. The response times for the variables labeled rowhash-level ACCESS locks and NO LOCKING modifier are almost identical because when NO LOCKING modifier was specified, the Optimizer applied a rowhash-level READ lock in the background. This lock has the same overhead as the row-level ACCESS lock.

Some request tools make it difficult to specify an ACCESS lock modifier. In spite of this, you can enforce explicit ACCESS locking by querying views and placing the appropriate LOCKING request modifier in their view definitions.

Rowhash-Level ACCESS Locks, Join Indexes, and Tactical Queries

The Optimizer propagates rowhash-level ACCESS locks to join indexes where appropriate. Assume the following single-table join index defined on the customer table. The UPI for the customer table is c_custkey. A query makes a request, specifying a value for c_name and requesting that a rowhash-level ACCESS lock be applied to the customer table.

   CREATE JOIN INDEX CustNameJI
    AS SELECT c_name, c_acctbal, c_mktsegment, c_range
    FROM customer
    PRIMARY INDEX (c_name);
   
    EXPLAIN
    LOCKING ROW FOR ACCESS
    SELECT c_acctbal, c_mktsegment, c_range
    FROM customer
    WHERE c_name='Customer#000000999';
Explanation
-----------------------------------------------------------------------
1) First, we do  a single-AMP RETRIEVE step from CAB.NAMEJI  by
way of the primary index "CAB.NAMEJI.C_NAME = 'Customer#000000999'"
with a residual condition of 
("CAB.NAMEJI.C_NAME = 'Customer#000000999'") into Spool 1, which is 
built locally on that AMP. The input table will not be cached in
memory, but it is eligible for synchronized scanning 
 locking row for access. The size of Spool 1 is estimated
with high confidence to be 1 row.

The join index covers the query and provides the requested customer table data based on the specific c_name value specified. The Optimizer applies the requested rowhash-level ACCESS lock to the join index row hash, not to the base table row hash.

Rowhash-Level ACCESS Locks, Group AMP Steps, and Tactical Queries

One of the key advantages of group AMP steps is that they avoid placing table-level locks. Instead of placing a table-level lock, group AMP operations exert rowhash-level locks on each row hash in the AMP group. The same performance-enhancing lock-level substitution also occurs when you explicitly request rowhash-level locking by specifying a LOCKING ROW FOR ACCESS modifier with your SQL request, as demonstrated by the EXPLAIN report generated for the following SELECT request. Notice the rowhash-level locks with ACCESS severity being applied in step 2 to the rows of both tables that are accessed by the request.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT p_name, p_type
     FROM lineitem, parttbl
     WHERE l_partkey=p_partkey
     AND   l_orderkey=5;
Explanation
-----------------------------------------------------------------------
  1) First, we do a  single-AMP RETRIEVE  step from CAB.lineitem by way
     of the primary index "CAB.lineitem.L_ORDERKEY = 5" with no
     residual conditions  locking row for access  into Spool 2
     (group_amps), which is redistributed by hash code to all AMPs.
     Then we do a SORT to order Spool 2 by row hash. The size of Spool
     2 is estimated with low confidence to be 1 row. The estimated
     time for this step is 0.01 seconds.
  2) Next, we do a  group-AMPs JOIN  step from Spool 2 (Last Use) by way
     of a RowHash match scan,  which is joined to CAB.parttbl locking 
     row of CAB.parttbl for access. Spool 2 and CAB.parttbl are
     joined using a merge join, with a join condition of ("L_PARTKEY =
     CAB.parttbl.P_PARTKEY"). The result goes into Spool 1
     (group_amps), which is built locally on that AMP. The size of
     Spool 1 is estimated with low confidence to be 1 row. The
     estimated time for this step is 0.11 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

ACCESS Locks, Joins, and Tactical Queries

When a query for which a rowhash-level ACCESS lock has been requested also includes a join operation, the Optimizer applies the rowhash-level ACCESS lock request to both tables if both are eligible.

The Optimizer plan generated for the following request is single-AMP because both tables are joined on their common orderkey UPI columns.

    EXPLAIN
    LOCKING ROW FOR ACCESS
    SELECT l_quantity, l_partkey, o_orderdate
    FROM lineitem, ordertbl
    WHERE l_orderkey=o_orderkey
    AND   o_orderkey=832094;
Explanation
-----------------------------------------------------------------------
  1) First, we do  a single-AMP JOIN  step from CAB.ordertbl by way of
     the unique primary index "CAB.ordertbl.O_ORDERKEY = 832094" with
     no residual conditions, which is joined to CAB.lineitem by way of
     the primary index "CAB.lineitem.L_ORDERKEY = 832094"  locking 
     row of CAB.ordertbl for access and row of CAB.lineitem for 
      access. CAB.ordertbl and CAB.lineitem are joined using a
     merge join, with a join condition of ("CAB.lineitem.L_ORDERKEY =
     CAB.ordertbl.O_ORDERKEY"). The input tables CAB.ordertbl and
     CAB.lineitem will not be cached in memory, but CAB.ordertbl 
     is eligible for synchronized scanning. The result goes into 
     Spool 1(one-amp), which is built locally on that AMP. The 
     size of Spool 1 is estimated with low confidence to be 35
     rows. The estimated time for this step is 0.03 seconds.

Rowhash-Level ACCESS Locks in Tactical Queries Are Compatible With Aggregates

If aggregation can be performed as a single-AMP operation, the Optimizer honors explicit rowhash-level ACCESS lock requests. An example might be the following request, where l_orderkey is the NUPI of the lineitem table and only a single row hash needs to be locked to satisfy the request.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT l_quantity, COUNT(*)
     FROM lineitem
     WHERE l_orderkey=382855
     GROUP BY l_quantity;
   
Explanation
-----------------------------------------------------------------------
  1) First, we do a  single-AMP SUM  step to aggregate from CAB.lineitem
     by way of the primary index "CAB.lineitem.L_ORDERKEY = 382855"
     with no residual conditions, and the grouping identifier in field
     1029 locking row for access. Aggregate Intermediate Results are
     computed locally, then placed in Spool 3. The input table will
     not be cached in memory, but it is eligible for synchronized
     scanning. The size of Spool 3 is estimated with low confidence to
     be 35 rows. The estimated time for this step is 0.03 seconds.
  2) Next, we do a  single-AMP RETRIEVE  step from Spool 3 (Last Use) by
     way of the primary index "CAB.lineitem.L_ORDERKEY = 382855" into
     Spool 1 (one-amp), which is built locally on that AMP. The size
     of Spool 1 is estimated with low confidence to be 35 rows. The
     estimated time for this step is 0.04 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

If you explicitly request a rowhash-level ACCESS lock and the query performs all-AMP aggregations, then the Optimizer upgrades the lock to a table-level ACCESS lock.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT SUM (l_quantity), SUM (l_extendedprice), COUNT(*)
     FROM lineitem;
Explanation
-----------------------------------------------------------------------
  1) First,  we lock CAB.lineitem for access.
  2) Next, we do an all-AMPs SUM step to aggregate from
     CAB.lineitem by way of an all-rows scan with no residual
     conditions. Aggregate Intermediate Results are computed globally,
     then placed in Spool 3. The input table will not be cached in
     memory, but it is eligible for synchronized scanning. The size of
     Spool 3 is estimated with high confidence to be 1 row. The
     estimated time for this step is 36 minutes and 56 seconds.
  3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs. The size of Spool 1 is estimated with high
     confidence to be 1 row. The estimated time for this step is 0.67
     seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

Some query tools make it difficult to include an ACCESS lock modifier in SQL requests. If this is a problem at your site, you can instead enforce ACCESS locking by placing the LOCKING request modifier in views through which the queries access the base tables.

Locking for Tactical Query Updates

A simple update request that specifies a primary index value for the table cues the Optimizer to apply a rowhash-level WRITE lock to process the update. Rowhash-level WRITE or rowhash-level READ locks are not reported in the EXPLAIN text. This request updates the unindexed orders table column o_orderpriority by accessing a single row using the UPI defined on o_orderkey. Only a single AMP and a rowhash-level lock are used to process this request.

    EXPLAIN
    UPDATE orders
    SET o_orderpriority = 5
    WHERE o_orderkey = 39256
   
Explanation
-----------------------------------------------------------------------
  1) First, we do  a single-AMP UPDATE  from CAB.orders by way of
     the unique primary index "CAB.orders.O_ORDERKEY = 39256"
     with no residual conditions.
  -> No rows are returned to the user as the result of statement 1.

Locking for Complex Tactical Query Updates

The following query updates the same unindexed column (o_orderpriority) by accessing a single row using a UPI (o_orderkey), but also includes a join to lineitem rows on their common orderkey value.

If a complex update is single-AMP operation and there is an equality condition on the UPIs common to both joined tables (o_orderkey and l_orderkey in the example), then the generated query plan specifies a high-performing single-AMP merge update using rowhash-level locking, as you can see in Step 1 of the EXPLAIN report for the following UPDATE request:

    EXPLAIN UPDATE ordertbl
    FROM lineitem
    SET o_orderstatus = 'OK'
    WHERE l_orderkey = o_orderkey
    AND   l_shipdate = o_orderdate
    AND   o_orderkey = 5;
   
Explanation
-----------------------------------------------------------------------
  1) First, we do a  Single AMP MERGE Update  to CAB.ordertbl from
     CAB.lineitem by way of a RowHash match scan.
  2) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

Some complex updates might involve multiple all-AMP steps, which necessitate table-level locking. This is not an important performance issue if you only occasionally perform this type of complex update. However, if you must perform a significant number of such operations, the effects of their all-AMP operations combined with the collateral table-level WRITE locks they require are likely to impair scalability as a function of the increasing volume of all-AMP requests.