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 Not The Same Thing
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 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 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.
Dealing With Multirequest Transactions That Require a Lock on the Same Table
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:
- Job_1 requires a READ lock on table_a.
Table_a is free, so the lock is granted and job_1 begins.
- 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.
- 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.
- Job_4 requires a READ lock.
This conflicts with the queued WRITE lock, so job_4 is queued behind job_2.
- Job_5 requires an EXCLUSIVE lock.
An EXCLUSIVE lock conflicts with all other locks, so job_5 is queued behind job_4.
- 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, 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. |
||