ORA-60 Deadlock Detected Between INCTCM And WICTWS

(Doc ID 2352329.1)

Last updated on JANUARY 24, 2018

Applies to:

Oracle Work in Process - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Problem Statement
RA-60 DEADLOCK DETECTED between INCTCM and WICTWS

INCTCM and WICTWS are executed individually every 10 minutes in the instance.
Sometimes ORA-60 DEADLOCK DETECTED occurred and it can not be reproduced the issue at will.

Error
The alert log for the deadlock shows something like this:

*** MODULE NAME:(INCTCM) 2018-01-16 07:01:31.375
*** ACTION NAME:(Concurrent Request) 2018-01-16 07:01:31.375

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

   :
----- Information for the OTHER waiting sessions -----
   :

   application name: WICTWS, hash value=XXXXXXXXXXXXX
   action name: Concurrent Request, hash value=XXXXXXXXXX

 current SQL:
 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP SET COST_GROUP_ID = :B3 , TRANSFER_COST_GROUP_ID = NVL(TRANSFER_COST_GROUP_ID, :B2 ) WHERE TRANSACTION_TEMP_ID = :B1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=03s61gtq86zy3) -----
update MTL_MATERIAL_TRANSACTIONS_TEMP MMTT  set TRANSACTION_HEADER_ID=:b0,LAST_UPDATE_DATE=SYSDATE,LAST_UPDATED_BY=:b1,LAST_UPDATE_LOGIN=:b2,PROGRAM_APPLICATION_ID=:b3,PROGRAM_ID=:b4,REQUEST_ID=:b5,PROGRAM_UPDATE_DATE=SYSDATE,LOCK_FLAG='Y',ERROR_CODE=null ,ERROR_EXPLANATION=null  where (((((PROCESS_FLAG='Y' and NVL(TRANSACTION_STATUS,1) in (1,3)) and NVL(LOCK_FLAG,'N')='N') and TRANSACTION_MODE=3) and ROWNUM<=:b6) and exists (select 1  from ORG_ORGANIZATION_DEFINITIONS OOD where (OOD.ORGANIZATION_ID=MMTT.ORGANIZATION_ID and NVL(OOD.DISABLE_DATE,(SYSDATE+1))>SYSDATE)))

 

Steps to Reproduce

  1. INCTCM and WICTWS are executed individually every 10 minutes in the instance.

Changes

 

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