Blocker - Advanced SQL Engine - Teradata Workload Management

Teradata Vantage™ - Workload Management User Guide
Product
Advanced SQL Engine
Teradata Workload Management
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
nbl1556236178169.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1197
lifecycle
previous
Product Category
Software
Teradata Vantage
If a single-statement transaction is delayed by a throttle, it does not receive resources, such as database locks and AMP worker tasks, until it is released from the queue and starts to run. However, a request that is part of a transaction (multiple requests between BT/ET) may hold onto resources while waiting in the delay queue. TASM can automatically detect the following sequence of events:
  1. A transaction starts to run.
  2. One of the first requests in that transaction obtains locks, which will not be released until the entire transaction completes.
  3. The system is busy, so a throttle delays a later request in the transaction.
  4. Requests that are running now need the locks held by the request in the delay queue. These active requests cannot complete because they cannot get the locks they need.
  5. TASM cannot release the request in the delay queue because the active requests are not completing.

Sometimes blocked requests resolve themselves. TASM can also act when a throttle delays one request in a transaction. The Block Cycles parameter together with the Exception Interval parameter specifies how long TASM should wait before acting on a blocked request.

The Block Action parameter controls what TASM does when the number of Block Cycles has been met. TASM can act on a request in the delay queue in one of these ways:
  • Log that it is holding a resource needed by a running request
  • Abort it because it is holding a resource needed by a running request
  • Release it to run

If you select Abort or Release, TASM logs that action to DBC.TDWMEventLog.

By default, Block Cycles is set to 1 and Block Action is set to Release. If a blocked request occurs, the number of Block Cycles should be enough to let the system try to resolve the block normally. After the waiting interval, TASM lets the blocking request run so it can free locks for other requests. If Exception Interval is set to 60 seconds and Block Cycles is set to 2, two 60-second cycles (2 minutes) must pass before TASM acts. If Block Cycles is set to Off, TASM does not resolve blocked requests.

One drawback to TASM blocked request resolution using the Release option is that it makes concurrency limits less strict. For example, if the throttle limit is 5, 6 or more requests may be running. When a throttle count is over limit, new requests stay in the delay queue longer waiting for the throttle count to decrease. If many transactions use TASM blocked request resolution, some work could be so significantly delayed in starting that overall workload management is affected. Evaluate TASM blocked request resolution carefully and monitor the impact of it on other work.