deadlock detected while waiting for resource

talsimad · Post by **talsimad** » Thu Mar 29, 2007 6:59 am

I'm trying to update an Oracle table with few key columns and sometimes the job abort with the fatal error: "deadlock detected while waiting for resource".
did someone know how to avoid this error.
i'm working on datastage px 7.01 on unix aix server.

best regards

talsimad · Post by **talsimad** » Thu Mar 29, 2007 7:05 am

the stage oracle is in parallel mode i khow that if i force the stage to be in sequential mode the problem will be resolved but the it will be executed slowly.
i'm trying to add specific options to avoid the problem....

chulett · Post by **chulett** » Thu Mar 29, 2007 7:24 am

Partition your data properly to ensure your parallel streams aren't trying to update the same data.

talsimad · Post by **talsimad** » Thu Mar 29, 2007 7:29 am

thanx
i already do it, i have partionned with the round robin method but it seem that the problem presist

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2007 3:40 pm

Are you attempting to write to the table while still selecting from the same table? This can lead to a self-deadlatch situation.

thebird · Post by **thebird** » Sat Mar 31, 2007 4:14 am

Try a hash partition... we had this problem when he had duplicate records in the data. A hash partition solved the problem for me.

chulett · Post by **chulett** » Sat Mar 31, 2007 7:31 am

talsimad wrote:i already do it, i have partionned with the round robin method but it seem that the problem presist

Then, perhaps, you don't already do it.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 31, 2007 10:54 am

Are you updating the same table from which you are selecting?

csrazdan · Post by **csrazdan** » Tue Apr 03, 2007 9:11 pm

You can get "deadlock detected while waiting for resource" error if you have multiple DML statements based on the same key in one transaction. You can avoid this error if you set Oracle commit level to 1. This will solve your problem but it will add an overhead of each record commit.

Alternatively, you can add a remove duplicate stage in the job stream and select the last record for updation.

Hope it helps........

DSXchange