Most blocks are momentary and do not require attention. However, if a block persists, you can investigate it to identify the cause and determine whether further action is required.
- Use the Teradata Viewpoint Query Monitor portlet to monitor the parameters related to query blocks and delays.
State Description Blocked Indicates that a session query is held up by a lock on an object that the query is attempting to access. Blocked Time How long the query has been blocked.
Momentary locks are a normal part of database operation. Persistent locks may indicate:
- A runaway query or hung query that cannot complete and release its locks, and which should be aborted
- A long-executing query, such as large-scale update or backup operation, which should be rescheduled to avoid resource contention
Delayed Indicates that the query is in a delay queue caused by a workload rule.
- Click the session ID for the query to access the Details View.
- If a query is blocked, see the Blocked By tab for details
- If the query is delayed, the Delay tab provides details about the cause of the delay
- You can also use the Lock Viewer portlet to find out additional details about the block:
- Block Time
- Blocked User
- Blocking Level
- Blocking User
Additional Tools for Analyzing Lock Problems
The following table provides suggestions for analyzing and solving lock problems.
|Lock Display||Transaction locks||Determine which session is holding the lock that blocks others.|
|Query Session||Blocked session||Abort the session causing blocked transactions.|
|Show Locks||Host utility (HUT) locks; that is, locks placed by a client-based utility, such as Archive and Recovery||Submit RELEASE LOCKS as either an Archive and Recovery command or an SQL statement.|