deadlock detected while updating oracle table

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
getsatish_gk
Participant
Posts: 104
Joined: Sat Dec 24, 2005 1:26 am
Location: Bengaluru

deadlock detected while updating oracle table

Post by getsatish_gk »

Hi All,

Did any one faced this issue when updating data in oracle 9 table using oracle enterprise stage?

sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sure, plenty. Have you tried searching the forums for that error?
-craig

"You can never have too many knives" -- Logan Nine Fingers
umamahes
Premium Member
Premium Member
Posts: 110
Joined: Tue Jul 04, 2006 9:08 pm

Post by umamahes »

Do you have any bitmap indexes on this table.If you have any please disable them before updating and also do proper partitining make sure all records with same key will be in the same partition.
HI
dashpriya
Participant
Posts: 28
Joined: Thu Mar 09, 2006 12:57 am
Location: mumbai
Contact:

Post by dashpriya »

Please check if you are using same table for fetching the records and same table to update or insert the records in same job.This situation also can create deadlock.
getsatish_gk
Participant
Posts: 104
Joined: Sat Dec 24, 2005 1:26 am
Location: Bengaluru

Post by getsatish_gk »

I finally have to choice server job to update a table.

- checked with increasing INITRANS on table
- And not doing any other tasks other the updating.
sateeshbabu
Participant
Posts: 48
Joined: Sat Oct 22, 2005 9:56 am

Post by sateeshbabu »

Satish,

You can use even parallel job but you have to verify two things

1. Check whether you rae using proper key columns in job based on the table definition

2. Put the execution mode of the job in sequential.

Regards,
Sateesh.M
getsatish_gk
Participant
Posts: 104
Joined: Sat Dec 24, 2005 1:26 am
Location: Bengaluru

Post by getsatish_gk »

Is this sateeshbabu mandepudi?

Well i changed to sequential mode. just to sack for work and it works.

But the real question remains is why parallel updatation doesn't work?.
is this something underlying database doesn't support it?

I have four nods and everything DBA comes and says datastage tried to update the table four times and got locked itself! :!:
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Possibly the same key arriving to Oracle from more than one partition?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
crouse
Charter Member
Charter Member
Posts: 204
Joined: Sun Oct 05, 2003 12:59 pm
Contact:

Post by crouse »

I've had the same issue lately and DBA's are stumped. INITRANS are increased on the table and the index. With commit set at 5000 and 4 nodes, always have the issue. I've set commits to 4, then to 10 and it keeps running ok. It seems to be a matter of keeping sessions on the nodes out of each other's way by making them commit more often. I can't say this will work every time in every environment (dev, qa, prod). But, at least I'm running more nodes than 1 and performance is better than on 1 or server, albeit not great performance.
Craig Rouse
Griffin Resouces, Inc
www.griffinresources.com
Gopinath
Participant
Posts: 52
Joined: Wed Apr 25, 2007 2:18 am
Location: Chennai

Duplicate warnings

Post by Gopinath »

Hi,

Iam getting a warning in Lookup stage like,

"Lookup: Ignoring duplicate entry at table record 9; no further warnings will be issued for this table"


Iam trying to get n links from same copy stage and hence ends up with this warning.
Any solution for removing this warnings.


Thanks.
Gopinath
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:? Come on, what the heck does this have to do with the current topic? Hint: nothing. Please start your own post on this subject.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jherr22
Premium Member
Premium Member
Posts: 56
Joined: Mon Jan 29, 2007 3:24 pm
Location: Denver
Contact:

Post by jherr22 »

If running in parallel, at the Oracle stage, click the INPUT tab, then choose partition. Select HASH and select the same columns that comprise the keys for the table you are loading. This will ensure that all the duplicate keys go down the same "path".
Post Reply