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 the request 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, that request is considered to be blocked. See Definition of an Explicit Transaction.
Any incompatibility with an EXCLUSIVE lock can cause 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 puts a request in a lock queue, the request runs when arriving at the head of the queue. Blocked requests do not time out, but remain in the queue until reaching its head. Requests that reach the queue head are granted their awaited locks and run.
The only requests the Lock Manager never enqueues are those explicitly specified with a LOCKING request modifier and the NOWAIT option. Such a request stops immediately if unable to acquire the specified lock, and you must resubmit the stopped 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 such an endless queue of blocked requests.
For example, a request that needs an EXCLUSIVE lock can be submitted first or last, depending on its processing time and 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 has a long processing time, consider submitting the request as a batch job to run during off hours.
Dealing with Multirequest Transactions
Explicit multirequest transactions must 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 a large number of requests or demands excessive processing time.
For example, consider the following scenario:
| Stage | Transaction Number | Process | ||
|---|---|---|---|---|
| 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. |
||