DSXchange

Hi All,

Our parallel job design as below which runs in multiple nodes,

Oracle Connector --> Tr (4 Output links)--> Oracle Connector.

(4 Links out of Transformer and connected to one target Oracle connector).

Transformer and Target Oracle connector runs is sequential mode.
In the Oracle connector link ordering and record ordering for all records are defined.
Target Oracle connector performs delete for a row, update for a row, insert of all records and finally delete for a row in each of its input links.

Our job is failing in deadlock issue in some cases.

Can you please suggest whats the reason for the issue and solution to fix the same.

TIA.

Point to add all the target activities are happening on the same table.

How are your transactions organized? In particular, how many rows per transaction for each of the CRUD operations?

If the number of rows per transaction is anything other than 1, you're almost certain to run into deadlocks with this design.

Transformations are like, the links which perform delete and update will have one record and other insert link will have more records.

So totally 3 links will have one record to target and rest one link has more records for insert.

as the deadlock error comes out of Oracle, it is pretty obvious that there is same record going for more than one operation at the same time.

For debugging purpose, try removing the one link each time and run the job. you will get to know the link and what combination is still receiving/processing the same record which runs into deadlock. and you can change the design accordingly

Could you please tell me how huge is the data?

Thanks for the updates.

mouthou,
I have a insert link and other three update/delete links. As per the link ordering and record ordering, these activities need to be performed in sequential order.

Not sure how this is being violated.

anudpETL,
In a batch I am getting around 1 M records.

yes you are logically correct as per the link ordering but I am thinking in the lines of duplicate rows getting into one particular link itself. We had such issues with duplicate data getting processed by different partition in the same run. If you have test DB, try to load the data from one link at a time, then you will get more clarity of the data pattern.

In a broader sense, are you sure that there is no other job trying to access the same table and the same row for DML operation around this job runs?

...Only wait to avoid the deadlock will be controlling the size of your transactions...I would follow Ray's respond to the point

Partitioning will be important, too. You need to ensure that each key value is processed on only one node, if you're planning to implement parallel connections.