DEADLOCK with MERGE INTO EH u USING as the victim statement

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

After upgrading to 12.3.2 HF1 or 12.3.3 you may see errors similar to:

U00003592 UCUDB - Status: '' Native error: '60' Msg: 'ORA-00060: deadlock detected while waiting for resource'
U00003524 UCUDB: ===> Time critical DB call!       OPC: 'SLCU' time: '10:428.380.000'
U00029131 UCUDB - List of bind parameters:
U00029132 UCUDB: 'EH_AH_Idnr         = 1235482'
U00003525 UCUDB: ===> 'SELECT * FROM EH WHERE EH_AH_Idnr = ?'
U00000006 DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.
U00003594 UCUDB Ret: '6' opcode: 'EXEC' SQL Stmnt: 'MERGE INTO EH u USING (   SELECT /*+ INDEX (EH NK_EH_OH_Idnr) */ EH_AH_Idnr, row_number() OVER (ORDER BY EH_AH_Idnr) rnum    FROM EH WHERE EH_OH_Idnr = ? AND EH_Status <= 1799 ) s ON (u.EH_AH_Idnr = s.EH_AH_Idnr) WHEN MATCHED THEN UPDATE SET u.EH_USR_Idnr = EH_USR_Idnr'
U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '12:429.259.000'
U00029131 UCUDB - List of bind parameters:
U00029132 UCUDB: 'EH_OH_Idnr         = 4564656'
U00003525 UCUDB: ===> 'MERGE INTO EH u USING (   SELECT /*+ INDEX (EH NK_EH_OH_Idnr) */ EH_AH_Idnr, row_number() OVER (ORDER BY EH_AH_Idnr) rnum    FROM EH WHERE EH_OH_Idnr = ? AND EH_Status <= 1799 ) s ON (u.EH_AH_Idnr = s.EH_AH_Idnr) WHEN MATCHED THEN UPDATE SET u.EH_USR_Idnr = EH_USR_Idnr'
U00003434 Server routine  'JPEXEC_R/CHKPARA' required '0' minutes and '12,443' seconds for processing.

 

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Cause

Incorrect rollback handling within the CHKPARA (check parallel tasks) routine. 

Resolution

Workaround:

Remove "max parallel" for JOBS or do not utilize a HOSTG.

 

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation Engine

Automation.Engine 12.2.7 - Available
Automation.Engine 12.3.3HF4 - Available
Automation.Engine 12.3.4 - Available