Apparently, a deadlock issue is related to resource contention between 2 actions.
Then, what could be the resources in this case?
At first, you can think of the real row-level contention.
Normally, it results from the abnormal Application design or replication product defects.
At this time, I would like to focus on other resource-related deadlock issues(block-level contention).
One of those reasons is ITL shortage issue
What is Interested Transaction List (ITL) ?
It is a simple data structure called "Interested Transaction List" (ITL), a list that maintains information on transaction.
The transaction identifier will be stored as an entry in the ITL in the header of the data block.
The ITL contains several placeholders (or slots) for transactions.
ITL slots are required for every transaction.
How many slots are typically available in ITL ?
During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL.
The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.
Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.
If there is available space in the block, the number of ITL should increase to the MAXTRANS. Right?
If that is the case, why the short of ITL issue happens?
Normally the speed of growing up is not fast enough to avoid ITL shortage issue.
Eventually, it ends up post slow issue OR blocking issues. To make it worse, deadlock issue could happen.
Ok. let's look at the diagram below for easy understanding about Oracle dead lock and ITL.
Oracle data block diagram describing a dead lock issue
Initially, there are 3 SQL Threads. You can think of it as a Transaction. And, we have 2 data blocks. After the step #4, all ITL slots are consumed.
Eventually, when SQL Thread#2 is going to modify the block 2, it must wait until ITL is available to acquire.
SQL Thread#3 will encounter ORA-60 error as it is a holder of block 2 and waiter of block 1.
This explains that we could have a deadlock issue because there is no available ITL slot.
It is different from the row level locking.
What should I do to avoid this issue?
At least, for heavy tables, please keep higher value of INI_TRANS at the first stage of your implementation. Apparently, that would save your day. If you couldn't reorganize all of tables and indexes, please make sure that you should do for the unique index which should be the first place you consider to change(It has a higher chance of much more contention.).
Then, How to increase INI_TRANS?
There are 2 ways to accomplish it.
You can change the value by running ALTER command, but only newly inserted rows are affected.
The other method is Reorganization of TALBLE or INDEX, in this case all rows are affected with new INIT_TRANS value.
What about SHAREPLEX_TRANS internal table in SharePlex?
This table saves transaction information of each session, and usually there are lots of block contention.
So, SharePlex development specified the INI_TRANS value with 20 by default.