Return
-
Title
Session # is blocked by session #, wait for blocker to release lock. -
Description
Your MTP (Multi Thread Post) goes idle and there's no apparent error beside warnings about blocked sessions.
For example:
[32771] 12/06/06 20:42 Notice: session=5 is blocked by session=2. They are in different threads. Will wait for blocker to release lock. [sp_opst_mt (for o.<sid>-o.<sid> queue <queue_name>)/30439] -
Cause
In this example, one session is blocked by another session. Normally, if first session get enough time to complete it's transaction, the blocked sessions will resolve itself. However, if the initrans is too small, this will also cause blocked sessions. -
Resolution
For this example, there's a few thing you need to check and change if need be:
1. Check if the problematic table and it's indices has a large enough initrans. It's 1 usually by default. We recommend 10-20 for your problematic tables and indices.
2. Check your SP_OPO_THREADS_MAX to make sure it's => 128. The value of SP_OPO_THREADS_MAX should be set to the default value of 128. If it is decreased, then chances of deadlocks will increase.
3. In Shareplex version 5.3.2 and newer, we introduce 2 Shareplex parameter to reduce the number of block session warning notice, SP_OPO_MAX_OEXN_TIME & SP_OPO_MAX_QUEUE_SIZE. If you get the blocked sessions warning too frequently, we recommending setting SP_OPO_MAX_OEXN_TIME to 180 and SP_OPO_MAX_QUEUE_SIZE to 500, this will give the MTP more time and resources to avoid blocked session errors.
SP_OPO_MAX_OEXN_TIME: This parameter controls the maximum duration, in seconds, that a SQL thread may spend in a call to OCIStmtExecute ( ) before the timekeeper thread defaults to a deadlocked position and forces the Multi-threaded Post process to exit. Default value 60
SP_OPO_MAX_QUEUE_SIZE: This parameter controls the maximum size (in messages) of the waiting queue for the SQL threads established for multi-threaded Post. The size of the waiting queue is 0 when the main thread has not dispatched any messages to the SQL thread. Default value 100
Note: The parameters specified are tuning parameters, so values given are suggested values and may need to be adjusted accordingly.