Error: "ORA-00060: deadlock detected while waiting for resource" While Running An ETL For Unifier Source (Using ODI Configured With More Than One Data Source) Executing "DELETE FROM SNP_SESSION WHERE (SESS_NO = ?)"

(Doc ID 2381388.1)

Last updated on APRIL 03, 2018

Applies to:

Primavera Data Warehouse - Version 17.12.0.0 and later
Primavera Analytics - Version 17.12.0.0 and later
Primavera Analytics Cloud Service - Version 17.12.0.0 and later
Information in this document applies to any platform.

Symptoms

While running an ETL for a Unifier source (through ODI, with Data Warehouse configured for more than one data source), the following error occurs:

ERROR
-----------------------
 Error Code: 60
Call: DELETE FROM SNP_SESSION WHERE (SESS_NO = ?)
  bind => [1 parameter bound]
Query: DeleteObjectQuery(oracle.odi.domain.runtime.session.OdiSession P6_GLOBAL_W_RESOURCECODETYPE_DS_PHYSICAL)
  at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
  at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)
  at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964)
  at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633)
  at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
  at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
  at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)
  at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
  at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
  at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:210)
  at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:104)
  at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:218)
  at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
  at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:803)
  at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
  at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:119)
  at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
  at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
  at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
  at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
  at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1790)
  at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:342)
  at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:296)
  at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1444)
  at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
  at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:278)
  at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169)
  at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:134)
  at oracle.odi.core.persistence.jpa.JpaOdiTransactionManager.doCommit(JpaOdiTransactionManager.java:387)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724)
  at oracle.odi.core.persistence.transaction.support.TransactionManagerSpringAdapter.commit(TransactionManagerSpringAdapter.java:67)
  at com.oracle.pgbu.bi.odi.runner.OdiPurgeStaleLog.b(SourceFile:117)
  at com.oracle.pgbu.bi.odi.runner.OdiPurgeStaleLog.purgeStaleLogs(SourceFile:49)
  at com.oracle.pgbu.bi.odi.runner.OdiLoadPlanRunner.a(SourceFile:120)
  at com.oracle.pgbu.bi.odi.runner.OdiLoadPlanRunner.main(SourceFile:467)
Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

ADDITIONAL INFORMATION
-------------------------------
After the initial failure, the ETL did kick off again a second time and completed successfully. However, the log file for the second ETL showed context for the first ETL which executed but failed.

STEPS TO REPRODUCE
-------------------------
The issue can be reproduced at will with the following steps:

  1. Login ETL Home
  2. Click on staretl2 > schedule
  3. Set a schedule for the ETL to run
  4. Note the reported issue which occurs.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms