Oracle deadlock when trying to upsert.

mekrreddy · Post by **mekrreddy** » Tue Sep 08, 2009 5:59 pm

Hello,

Couple of our jobs aborted in prod with oracle deadlock, when trying to upsert. also got this message in log: oci_invalid_handle
The jobs are running fine in Dev. We are using 4 node config file in production. partition is Auto.

Please give your views on this.

Thanks in advance...
reddy.

chulett · Post by **chulett** » Tue Sep 08, 2009 7:19 pm

Typical situation where dev data does not reveal an issue that production data does. Does it work fine with a 1 node config file?

mekrreddy · Post by **mekrreddy** » Tue Sep 08, 2009 7:47 pm

thanks hullet,

yes, even code run successfully in systest.

We have huge tables, 50mn records on one table , so we did not test with one node config file yet,

mekrreddy · Post by **mekrreddy** » Wed Sep 09, 2009 7:33 pm

When we run the same job with 1 node and 2 node config files, runs successfully!!! but some tables are huge, we can't afford to run with 1 or 2 node, but what causing oracle deadlock? using 4 nodes.?? with auto partition.

Thanks in advance..

chulett · Post by **chulett** » Wed Sep 09, 2009 8:46 pm

Your parallel processes are deadlocking. This is where you would need to override the auto partitioning and control it so it doesn't deadlock. Probably hash partitioned over the keys involved would help, I'd have a chat with your DBA and confirm.

kduke · Post by **kduke** » Thu Sep 10, 2009 10:48 am

If you have an uncommitted insert followed by another update on the same key then there is your issue. When the first one commits the second changes from an insert to an update. You may need to rethink your logic.

mekrreddy · Post by **mekrreddy** » Thu Sep 10, 2009 6:18 pm

Thanks Hullet and Duke

we changed the partition from auto -> Hash, no success!!
changed UPSERT ROW COMMIT INTERVAL from 5000 to 1000, no success.

Interestingly we run the job against same data(prod) in testing environment, and testing database, its works fine!!(does not work in prod)

is this completely an application issue? or DB issue as well..

please share your thoughts..

keshav0307 · Post by **keshav0307** » Thu Sep 10, 2009 8:01 pm

have you hash partitioned based on the unique index on the table?

mekrreddy · Post by **mekrreddy** » Thu Sep 10, 2009 8:05 pm

thanks Keshav,

yes we hash partitioned on the unique key..
no success.

chulett · Post by **chulett** » Thu Sep 10, 2009 9:15 pm

Talk to your DBA, make sure he lets you in on when multiple processes writing to the same table can deadlock. Adjust the job accordingly.

mekrreddy · Post by **mekrreddy** » Tue Sep 15, 2009 7:59 pm

The issue is resolved, DBA had to change INITTRANS Value from 1(Default), to 8. There was no issue from Datastage.

Thanks all.

DSXchange

Oracle deadlock when trying to upsert.

Oracle deadlock when trying to upsert.

Thank you all!!!