15.10 - Locking Issues With Tactical Queries - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    The following update operation is a more complex version of the update operation presented in “Locking for Tactical Query Updates” on page 750. 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.