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
- Database
- Table
- Delay
- Blocked User
- Blocking Level
- Blocking User
Additional Tools for Analyzing Lock Problems
The following table provides suggestions for analyzing and solving lock problems.
| Tool | Analysis | Solution |
|---|---|---|
| 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 DSA | Submit RELEASE LOCKS as either an Archive and Recovery command or an SQL statement. |