Deadlock while updating Oracle table
Moderators: chulett, rschirm, roy
Deadlock while updating Oracle table
Hi All,
While trynig to update a Sales fact table using 4 node the job is failing with the following error:
ORA-00060: deadlock detected while waiting for resource
It's a update only query and input is hash partitioned on keys.
Target table is range partitioned on date and job is running fine when data
is distributed within two nodes or single node.There is no duplicate record.
Also MAX_TRANS is not set at DB level.
-Can a range partitioned table lead to deadlock if more than one node try to update records belonging to single partition ?
-Is there any way to avoid deadlock even with 4 nodes ?
Thanks for your help!
Ankita
While trynig to update a Sales fact table using 4 node the job is failing with the following error:
ORA-00060: deadlock detected while waiting for resource
It's a update only query and input is hash partitioned on keys.
Target table is range partitioned on date and job is running fine when data
is distributed within two nodes or single node.There is no duplicate record.
Also MAX_TRANS is not set at DB level.
-Can a range partitioned table lead to deadlock if more than one node try to update records belonging to single partition ?
-Is there any way to avoid deadlock even with 4 nodes ?
Thanks for your help!
Ankita
Re: Deadlock while updating Oracle table
Hi
change oracle stage partition to sequential.
Thanks
Sanjay
change oracle stage partition to sequential.
Thanks
Sanjay
ankita wrote:Hi All,
While trynig to update a Sales fact table using 4 node the job is failing with the following error:
ORA-00060: deadlock detected while waiting for resource
It's a update only query and input is hash partitioned on keys.
Target table is range partitioned on date and job is running fine when data
is distributed within two nodes or single node.There is no duplicate record.
Also MAX_TRANS is not set at DB level.
-Can a range partitioned table lead to deadlock if more than one node try to update records belonging to single partition ?
-Is there any way to avoid deadlock even with 4 nodes ?
Thanks for your help!
Ankita
Of course.ankita wrote:As per my understanding, changing Oracle partitions into sequential will again make the load sequential, but is there any way to avoid deadlock even with parallel loading ?
So basically, the answer you got is not a good answer. (it amused me somehow)
And I don't have a good answer to you neither as i'm experiencing the same issue. This looks like a bug.
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
It seems like this is caused by the parallel configuration. I was using 6 nodes. I tried 8 and 4 nodes and I don't get the deadlock anymore.
Now I don't know yet if it's the number of nodes, or the location of the disk and scratch. On 6 nodes I had it on the same filesystems. On 4 and 8, I splitted it across 4 filesystems.
Now I don't know yet if it's the number of nodes, or the location of the disk and scratch. On 6 nodes I had it on the same filesystems. On 4 and 8, I splitted it across 4 filesystems.
roceller wrote:Of course.ankita wrote:As per my understanding, changing Oracle partitions into sequential will again make the load sequential, but is there any way to avoid deadlock even with parallel loading ?
So basically, the answer you got is not a good answer. (it amused me somehow)
And I don't have a good answer to you neither as i'm experiencing the same issue. This looks like a bug.
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Only a small amount of thought will explain why hash partitioning on the column(s) in the WHERE clause is the solution, and why not having it might cause a deadlock, particularly if rows/transaction > 1.
So please think about it.
So please think about it.
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.
Now i'm getting a deadlock too on another job even if I try 4 or 8 nodes.keshav0307 wrote:i had similar problem and hash partitioning help me.
hash partition the input, on the columns used in the where clause.
I just have one primary key (used on the update condition). Hash partitioning on the Update input stage did not help me.
Now, I wonder how my other job worked fine after I changed from 6 to 8 nodes.
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Find out from your DBA on which record the deadlock is occurring - for example it might be an update being performed by a trigger from each node, but of which you were unaware.
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.
I've done this and it was fine for some days, but today, I got the same deadlock. I even had it checked "unique" to remove duplicates even though I'm sure there is no duplicate. This problem is just happening occasionally on the same set of data.madhukar wrote:Hash Partition and sort on the update key should resolve your problem.
If you are updating a particular set of records, putting that filter in update will you help you better
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
FINALLY!!!
I think we solved this mysterious deadlock issue!!!
It's a table setup issue.
I think we solved this mysterious deadlock issue!!!
It's a table setup issue.
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
Sorry took me sooo long to follow up..... just in case for those looking for answers... the DBA has to recreate the Oracle table segment with higher INITRANS and PCTFREE values... i don't remember it now exactly. Just work with your DBA.
Roceller Alvarez - Sr. ETL Designer/Developer and Administrator - also a Web Guru
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>
<a href="http://www.i-vibe.com/">DATASTAGE DEVELOPER RESOURCE & TIPS PORTAL</a>