deadlocks while insert then update on oracle
Posted: Tue Mar 26, 2013 3:06 am
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
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