Oracle connector - Deadlock issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 39
- Joined: Tue Feb 28, 2012 12:59 pm
- Location: Chennai
Oracle connector - Deadlock issue
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.
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.
-
- Participant
- Posts: 39
- Joined: Tue Feb 28, 2012 12:59 pm
- Location: Chennai
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 39
- Joined: Tue Feb 28, 2012 12:59 pm
- Location: Chennai
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
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
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
-
- Participant
- Posts: 39
- Joined: Tue Feb 28, 2012 12:59 pm
- Location: Chennai
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?
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?
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.