General Information when post encounters blocking situation
A blocking condition occurred between two sessions in poster.
To troubleshoot set poster debug as follows:
sp_crl>set param SP_OPO_DEBUG_FLAG 0x1f0021ff
Wait till post stops and check the *opo* logs for these occurrences.
1. Failed due to row contention (How to figure it out?)
Look for the last message in the debug log and get the source rowid from it. Search the rowid throughout the debug file to see if here is other transaction on that rowid. If do then:
Solution - qview -p would able to handle this particular example
2. Array insert that is failing due to a collision on the unique index (How to figure it out?)
When you look at the opo debug log, the last message that Shareplex stuck on is an array insert.
Solution - For this, convert the unique index into a non-unique index (disable the unique constraint) and add the index into the hints file.
3. Init_trans on the table (How to figure it out?)
When looking at the opo log and the last message is an update and there is no row contention. This implied that they have an block contention. For block contention, you can have a block contention for the table or the index (cover in step 4). Check to see how many transactions is going at the time of the break failed (grep odr_trans *opo* | sort | uniq | wc -l). If the init_trans on the table is lower than the number of concurrent transaction then there is a possibility of that many transaction updating that block at the same time. Increase the init_trans to the appropriate number.
Solution - Increase the init_trans on the table. The table will have to be rebuild for the block to be re-migrated.
4. Init_trans on the index (How to figure it out?)
When looking at the opo log and the last message is an update and there is no row contention. This implied that they have an block contention. For block contention, you can have a block contention for the table or the index (cover in step 4). Check to see how many transactions is going at the time of the break failed (grep odr_trans *opo* | sort | uniq | wc -l). if you check the init_trans on table and it looks good then you need to look at the init_trans on the index as well. You could be coliding on the block of the index.
Solution - rebuild the index with a different init_trans setting and restart post.
