Page 1 of 1
esql complaint: ORA-00060: deadlock detected while waiting f
Posted: Thu Feb 02, 2006 5:55 pm
by dsedi
All,
one of my PX job( Which is triggering INSERT query) failing frequently by saying
Code: Select all
sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource
it's happening regularly on a particular table...
I want to confirm the issue is really with ORACLE or is it due to the parallel exection( on 2 nodes) of my job?
FYI...I am having a JOb parameter called "Disable operator combination" value as FALSE...is that creating issues...?
any idea's?
Thanks,Edi
Posted: Thu Feb 02, 2006 7:32 pm
by keshav0307
me too was getting this problem. but proper partioning help me.
your target table may be in upsert mode. so do proper input stream partitioning on keys.
Re: esql complaint: ORA-00060: deadlock detected while waiti
Posted: Thu Feb 02, 2006 7:37 pm
by kwwilliams
This
"Disable operator combination" value as FALSE
is not causing you any problems. The problem is that your record set has duplicate values in them. You could have the records in different partitions, or they could be in the same partitions in a large commit block. Because Oracle only locks at a record level you should know that you are inserting and/or updating a row multiple times. If you hash parition based on the key and then remove duplicates you should see this go away. You should also see the output of the link that removes duplicates be less than the input if I am correct.
Posted: Fri Feb 03, 2006 11:11 pm
by srividhya
Hi ,
set the INITRANS to more then 2 in oracle setting since you are runnind in 2 nodes. ALTER TABLE INITRANS 3
it worked for me
Srividhya
Posted: Thu Feb 09, 2006 2:30 pm
by tardifma
Hi
From what I understand, the problem could be a mix of Oracle and Datastage PX...
What is an Oracle Deadlock exactly:
It is when 1 session make a change on a particular row and another session (not the same) try to make another change on the same row... but the change will not be applied until the commit of the session 1 happen.
Suppose now that the session 2 makes a change a another row and after that the session 1 tries to update the same row... then session 1 will wait session 2 to commit...
In that particular case, both sessions will wait to commit... and a Deadlock will happen...
This is from an Oracle Perspective.
Now, from a Datastage perspective... For each node you have, Datastage create a connection (a session) to Oracle... Suppose that each node upsert the same rows... you'd have a deadlock...
Make sure you are not "Upserting" the same rows...
Thanks.
Posted: Wed Dec 14, 2011 4:24 pm
by madannitjam
I also faced the same issue and it was a duplicate issue.