deadlocks while insert then update on oracle

[mfranke]

Hi,
we are migrating from 7.5.3 to 8.7.
While loading data to oracle 11g via oracle connector some jobs produce deadlocks on the database, when loading over 100000 rows, when loading less it works.

The OCI function OCIStmtExecute returned status 1. Error code: 60, Error message: ORA-00060: deadlock detected while waiting for resource.

On the tables we make Insert then Update, then there are to sessions on it which causes the deadlock.
Only Insert or only Update Statement causes no deadlock. Update then Insert is no option because of constraints that cannot be matched then.

It seems, that the insert and the update statements each use one session that is processed parallel or short time serial.

Is that possible?

Kann das sein?
Any ideas to solve it (within one job, off course there can be 1 job for insert and 1 for update).


Many thanks,
Mario Johrend

[chulett]

Couple of questions. I'm assuming this is a multi-node job and that running it on a single node "solves" the problem. If true then you have a partitioning problem. If you haven't, try setting it to Hash and use the appropriate keys for the target in the hashing algorithm.

[rameshrr3]

I agree with whatever Craig says.Additionally , When running in sequential mode , try using a sort-merge collector ( on table keys) if its applicable to your stage. Duplicate records for table keys can cause issues also.

[priyadarshikunal]

Adding to the answers in above posts to explain what you might be experiencing.

Generally this problem comes, as Craig mentioned, due to two different sessions trying to update the same records or insert/update combination and hits the race condition. This can be avoided by sending all the records for that particular key in one session. Key based partitioning insures that.

Insert then update doesn't run in parallel, only when insert fails it will attempt update, however running on multiple nodes creates as many sessions as the number of nodes and in that case if there is multiple records for that key which is in different partitions, it may be sent for insert/update in different session causing the problem.

[mfranke]

running the oracle connector in sequential mode helps, now the problem is solved.

[prasannakumarkk]

to resolve the problem you are compromising the performance. is it fine?