Transaction handling in Parallel jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
Transaction handling in Parallel jobs
Hi everyone,
I have a parallel job that has failed due to a deadlock on one of the Oracle tables into which the job does inserts.
The approach I adapted was to see if there was any concurrent process that does any modification to the table and found out to be none.
But, I am not sure about the transaction commit process in Parallel jobs which could be another reason for the deadlocks.
Please advice if I am heading the right way in analyzing my issue.
Thank you,
Mukthi
I have a parallel job that has failed due to a deadlock on one of the Oracle tables into which the job does inserts.
The approach I adapted was to see if there was any concurrent process that does any modification to the table and found out to be none.
But, I am not sure about the transaction commit process in Parallel jobs which could be another reason for the deadlocks.
Please advice if I am heading the right way in analyzing my issue.
Thank you,
Mukthi
What led you to believe that the table was locked? or your process was deadlocked? If you job hangs then I suggest getting together with your dba to see if the connection even reaches the table and whats happenening at the database level. There might be live threads out there with your id which might have put the table in a locked state.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
If this is DataStage log then it would be nice to post the exact error message.
If you suspect its the commit level then there are two environment variables that you can set to control commit levels. Namely
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL
If you suspect its the commit level then there are two environment variables that you can set to control commit levels. Namely
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you have an active SELECT cursor on table abcd, then table abcd will be locked against INSERT operations. Reduce your transaction size to 1 row, and/or set to auto-commit. Even better, select the data into a staging area (a persistent Data Set or Lookup File Set) in a prior job to avoid a self-deadlock situation.
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: 19
- Joined: Mon May 01, 2006 11:27 am
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am