ORA-00060: deadlock detected
Moderators: chulett, rschirm, roy
ORA-00060: deadlock detected
Hi guys,
today in the middle in the project our DS Parallel jobs started to crash with this message:
CUS_CUST_CONTRACTS,1: Open failed for update.
Update is: ... (Some simple SQL)
sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource
It happens with all Orchestrate processes.
We have tried to turn on deadlock manager, but with no help.
We have not change any parameters in either Database (10g) or DataStage. Yesterday everything was fine!
Please help....
Drazen
today in the middle in the project our DS Parallel jobs started to crash with this message:
CUS_CUST_CONTRACTS,1: Open failed for update.
Update is: ... (Some simple SQL)
sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource
It happens with all Orchestrate processes.
We have tried to turn on deadlock manager, but with no help.
We have not change any parameters in either Database (10g) or DataStage. Yesterday everything was fine!
Please help....
Drazen
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is an Oracle error, not a DataStage error.
Something in your design, or something else that is running on Oracle at the same as your DataStage job, is causing a deadlock situation. For example, are you trying to update the same table that you're selecting rows from?
Something in your design, or something else that is running on Oracle at the same as your DataStage job, is causing a deadlock situation. For example, are you trying to update the same table that you're selecting rows from?
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.
Hi, we have created bitmap indexes on few tables yesterday afternoon. After we have dropped those indexes and recreated them as ordinary non-unique indexes, it works fine. I suppose that was creating a problem, but we should find some workaround. Does anybody use parallel upserts in the table with bitmap indexes enabled?
Thanks,
Drazen
Thanks,
Drazen
It is a problem with the way datastage handles the parallelism. try increasing the MAXTRANS database parameter for the table and the index on that table.
But if you dont want parallesim, include the $APT_CONFIG file parameter in the job and modify it for single node file. It should run fine.............
Thanks.
But if you dont want parallesim, include the $APT_CONFIG file parameter in the job and modify it for single node file. It should run fine.............
Thanks.
Bitmap Indexes are not something you want to be updating, if you can help it. Found this quote at a site I have bookmarked:
We drop and rebuild the few that we have at each end of the processing cycle.You must also be concerned about high-volume updates. Bitmap indexes are notoriously slow to change when the table data changes, and this can severely slow down INSERT and UPDATE DML against the target tables.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
I think what Ray is telling is true. I had a similar problem in one of my jobs. I discussed the deadlock issue with my DBA and team members. We found that i was updating the same row in parallel processes. Once for Insert and once for Update. I think i had selected the Insert and Update option. This was creating the deadlock.
So i created constraints in the transformer and explicitly asked the job to Insert or Update.
thanks
jay
So i created constraints in the transformer and explicitly asked the job to Insert or Update.
thanks
jay
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Something like this; the Lookup determines whether the row (key) already exists in the table (possibly against a key-only Data Set) and, on that basis, either sends the row to be inserted (insert only) or to be updated (update only).
Code: Select all
-----> Lookup -----> Insert
-----> Update
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.
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Thats a round about way of doing it, I had this situation before and it worked when I reset the array size to 1, have raised a case with IBM will post the solution if they come up with one, else will have to go the constraints way.
Thanks and Ray do you happen to know of the reviews on advanced EE training from IBM?
Thanks and Ray do you happen to know of the reviews on advanced EE training from IBM?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Student feedback forms are returned to IBM. After that they are analyzed to look for ways to improve the classes. The instructor quickly looks at them (maybe) at the end of class, and never sees them again. If there are issues that IBM has with the instructor, presumably IBM gets in touch with the instructor. Never happened to me, though, so I can't be sure on that one. I guess the fact that I get repeat business is some form of feedback.vinaymanchinila wrote:Ray do you happen to know of the reviews on advanced EE training from IBM?
Is that what you meant?
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.