Deadlock while updating Oracle table

ankita · Post by **ankita** » Thu Jun 21, 2007 12:23 pm

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

sanjay · Post by **sanjay** » Thu Jun 21, 2007 5:44 pm

Hi

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

ankita · Post by **ankita** » Fri Jun 22, 2007 7:23 pm

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 ?

roceller · Post by **roceller** » Fri Jul 13, 2007 3:30 pm

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 ?

Of course.
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 · Post by **roceller** » Wed Jul 18, 2007 10:18 am

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.

roceller wrote:
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 ?
Of course.
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.

bucks · Post by **bucks** » Wed Jul 18, 2007 12:54 pm

I think this is not a node configuration issue. it is more of a database issue.

Can you please check the sort option and hash partition on the primary keys and try ?

thanks
Sri

keshav0307 · Post by **keshav0307** » Thu Jul 19, 2007 6:47 am

i had similar problem and hash partitioning help me.
hash partition the input, on the columns used in the where clause.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 19, 2007 3:30 pm

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.

roceller · Post by **roceller** » Mon Aug 13, 2007 3:50 pm

keshav0307 wrote:i had similar problem and hash partitioning help me.
hash partition the input, on the columns used in the where clause.

Now i'm getting a deadlock too on another job even if I try 4 or 8 nodes.

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.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 13, 2007 4:21 pm

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.

madhukar · Post by **madhukar** » Tue Aug 14, 2007 9:05 am

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 · Post by **roceller** » Wed Aug 15, 2007 8:48 am

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

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.

roceller · Post by **roceller** » Wed Aug 15, 2007 2:35 pm

FINALLY!!!
I think we solved this mysterious deadlock issue!!!
It's a table setup issue.

chulett · Post by **chulett** » Wed Aug 15, 2007 3:52 pm

Meaning what, exactly?

roceller · Post by **roceller** » Thu Feb 26, 2009 1:36 pm

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.

DSXchange

Deadlock while updating Oracle table

Deadlock while updating Oracle table

Re: Deadlock while updating Oracle table