A request waiting in a lock queue is considered blocked (see Compatibility Among Locking Severities). A consume mode SELECT request that has not been granted a lock because a request is in a delay state is not considered blocked. However, if such a request is considered blocked if awakened and placed into a lock queue. 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 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, the request runs when it arrives at the head of the queue. A blocked request does not time out, but remains in the queue until reaching its head, when its requested locks are granted and it runs.
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). If such a request cannot acquire the specified lock, it ends immediately, and you must resubmit the ended request.
A deadlock is a lock contention situation that cannot be solved without intervention (see Deadlock). For Vantage, the intervention is accomplished by ending the younger request in the deadlock.
A retryable error is reported for a request that is aborted by a deadlock.
Dealing with Multirequest Transactions that Require Lock on Same Table
- 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 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. The request must 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
Review explicit multirequest transactions 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 result if the active transaction has multiple requests or demands excessive processing time.
For example, consider the following scenario:
| Stage | Process | |||
|---|---|---|---|---|
| Transaction Number | Request | Required Lock | 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. |
||