Blocked Requests | Transaction Processing | Teradata Vantage - 17.10 - Blocked Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

A request that is waiting in a lock queue is considered to be blocked (see Compatibility Among Locking Severities). A consume mode SELECT request that has not been granted a lock because it is in a delay state is not considered to be blocked. However, if such a request is awakened and is placed into a lock queue, it is then considered to be blocked. See Definition of an Explicit Transaction.

If you suspect that a request is blocked, you can use the Query Session utility (see Teradata Vantage™ - Database Utilities, B035-1102) to confirm or refute your suspicions about the status of the session.

Any incompatibility with an EXCLUSIVE lock can result in a queue of several blocked requests, all of which must wait until the system releases the blocking EXCLUSIVE lock.

Blocking and Deadlocking Are Different

When the Lock Manager places a request in a lock queue, it is known that the request will execute as soon as it arrives at the head of the queue. Blocked requests do not time out; they remain in the queue as long as it takes to reach its head, at which time they are granted the locks they are waiting for and execute.

The only requests the Lock Manager never enqueues are those explicitly specified with a LOCKING request modifier and the NOWAIT option (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). Such a request aborts immediately if it cannot acquire the specified lock, and it is the responsibility of the user to resubmit the aborted request.

A deadlock is a lock contention situation that cannot be solved without intervention of some kind (see Deadlock). For Vantage, the intervention is accomplished by aborting the younger request in the deadlock.

Note that a retryable error is reported for a request that is aborted by a deadlock.

Dealing with Multirequest Transactions that Require Lock on Same Table

When several requests that compete for the same table are submitted as separate, single-request transactions, Vantage resolves their locking requirements as illustrated by the following process:

  1. Job_1 requires a READ lock on table_a.

    Table_a is free, so the lock is granted and job_1 begins.

  2. While job_1 is still running, job_2 requires a WRITE lock.

    This conflicts with the active READ lock, so the WRITE lock is denied and job_2 is queued.

  3. Job_3 requires an ACCESS lock.

    ACCESS locks are compatible with both READ and WRITE locks (if job_1 completes, releasing the READ lock, then job_2 can begin whether or not job_3 still holds the ACCESS lock), so the ACCESS lock is granted and job_3 is allowed to run concurrently with job_1.

  4. Job_4 requires a READ lock.

    This conflicts with the queued WRITE lock, so job_4 is queued behind job_2.

  5. Job_5 requires an EXCLUSIVE lock.

    An EXCLUSIVE lock conflicts with all other locks, so job_5 is queued behind job_4.

  6. Job_6 requires an ACCESS lock.

    This conflicts with the queued EXCLUSIVE lock, so job_6 is queued behind job_5.

Resolving Blocked Requests

Careful session scheduling can prevent such an endless queue of blocked requests.

For example, a request that needs an EXCLUSIVE lock can be submitted first or last, depending on how long it takes to process and its function in relation to other requests. It should be run first if other requests depend on its changes.

If a request that needs an EXCLUSIVE lock takes a lot of processing time, consider submitting it as a batch job to run during off hours.

Dealing with Multirequest Transactions

Explicit multirequest transactions should also be reviewed for any scheduling concerns.

When competing locks are needed by multiple requests in a single transaction, Vantage automatically upgrades the locks for each request in turn, until the transaction is completed.

This handling protects an active transaction from being interrupted by new arrivals. However, a blocked queue can still result if the active transaction has many requests or demands excessive processing time.

For example, consider the following scenario:

Stage                                                                                                  Process
This transaction number … Has this request … That requires this lock … With this result …
1 1
SELECT …
FROM table_a
READ The READ lock on table_a is granted and the SELECT request begins processing.
2 2
INSERT
INTO table_a …
WRITE Transaction 2 is queued because its request for a WRITE lock on table_a is not compatible with the READ lock already in place on table_a.
3 1     Processing of the SELECT request from Transaction 1 completes.
INSERT
INTO table_a …
WRITE The READ lock on table_a is upgraded to a WRITE lock for the Transaction 1 INSERT request.

Transaction 2 remains queued and inactive, waiting for its WRITE lock request on table_a to be granted.