ORA-00060: deadlock detected

dorescan · Post by **dorescan** » Thu Nov 18, 2004 10:49 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Nov 18, 2004 2:15 pm

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?

dorescan · Post by **dorescan** » Thu Nov 18, 2004 2:23 pm

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

mouthou · Post by **mouthou** » Mon Nov 22, 2004 1:54 pm

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.

chulett · Post by **chulett** » Mon Nov 22, 2004 2:38 pm

Bitmap Indexes are not something you want to be updating, if you can help it. Found this quote at a site I have bookmarked:

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.

We drop and rebuild the few that we have at each end of the processing cycle.

vinaymanchinila · Post by **vinaymanchinila** » Wed Jan 25, 2006 2:16 pm

I agree with "mouthou", it looks like a DS bug, even though I have the insert array soze as 1 instead of the default 500, my job aborts with the "ORA-00060: deadlock detected while waiting for resource" and my DBA is sure there are no locks .

Jay · Post by **Jay** » Wed Jan 25, 2006 3:33 pm

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

vinaymanchinila · Post by **vinaymanchinila** » Wed Jan 25, 2006 3:39 pm

Can you eloborate on
"So i created constraints in the transformer and explicitly asked the job to Insert or Update. "

ray.wurlod · Post by **ray.wurlod** » Wed Jan 25, 2006 3:51 pm

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

vinaymanchinila · Post by **vinaymanchinila** » Wed Jan 25, 2006 3:57 pm

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?

ray.wurlod · Post by **ray.wurlod** » Wed Jan 25, 2006 5:26 pm

vinaymanchinila wrote:Ray do you happen to know of the reviews on advanced EE training from IBM?

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.

Is that what you meant?

Madhav_M · Post by **Madhav_M** » Sun Aug 06, 2006 10:14 am

Hi,
I am also facing the same issue! MAXTRANS value for the table to be updated set to 255. APT_CONFIG_FILE is set to 1way.apt.

After the above settings, job started updating around 100K records however 200K records are getting rejected!!

Any thoughts?

Thanks
Madhav