Special Locking Issues Raised by Tactical Queries
- 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 Teradata Vantage™ - Database Design, B035-1094 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 Teradata Vantage™ - Database Design, B035-1094 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.
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.