15.10 - Blocked Requests - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

A request that is waiting in a lock queue is considered to be blocked (see “Compatibility Among Locking Severities” on page 692). 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” on page 645.

If you suspect that a request is blocked, you can use the Query Session utility (see Utilities) 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.

The blocking behavior of HUT locks is identical to that of database transaction locks, as is the interactive blocking behavior of HUT and database transaction locks.

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 “LOCKING Request Modifier” in SQL Data Manipulation Language). 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” on page 722). For Teradata Database, 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.

When several requests that compete for the same table are submitted as separate, single‑request transactions, Teradata Database 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.

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.

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

When competing locks are needed by multiple requests in a single transaction, Teradata Database 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.