Oracle connector - Deadlock issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
techiexavier
Participant
Posts: 39
Joined: Tue Feb 28, 2012 12:59 pm
Location: Chennai

Oracle connector - Deadlock issue

Post by techiexavier »

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.
techiexavier
Participant
Posts: 39
Joined: Tue Feb 28, 2012 12:59 pm
Location: Chennai

Post by techiexavier »

Point to add all the target activities are happening on the same table.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
techiexavier
Participant
Posts: 39
Joined: Tue Feb 28, 2012 12:59 pm
Location: Chennai

Post by techiexavier »

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.
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Post by mouthou »

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
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

Could you please tell me how huge is the data?
anudpETL
techiexavier
Participant
Posts: 39
Joined: Tue Feb 28, 2012 12:59 pm
Location: Chennai

Post by techiexavier »

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.
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Post by mouthou »

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?
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

...Only wait to avoid the deadlock will be controlling the size of your transactions...I would follow Ray's respond to the point
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply