Converting Jobs from utilizing Sync to Queues cause unresponsive system and deadlock on FIFO tables

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Error Message :
20171022/140454.012 - U0000006 DEADLOCK
20171022/140454.012 - U0003594 UCUDB Ret: '6' opcode: 'EXEC' SQL Stmnt: 'UPDATE FIFO SET FIFO_Nr = FIFO_Nr + 1 WHERE FIFO_Name = ?'
20171022/140454.012 - U0003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '23:866.133.999'
20171022/140454.012 - U0029131 UCUDB - List of bind parameters:
20171022/140454.012 - U0029132 UCUDB: 'FIFO_Name = QUEUE.OAP.JACKAL'
20171022/140454.012 - U0003525 UCUDB: ===> 'UPDATE FIFO SET FIFO_Nr = FIFO_Nr + 1 WHERE FIFO_Name = ?'
20171022/140454.017 - U0003434 Server routine 'JPEXEC_R/EXT_CHK' required '0' minutes and '44,720' seconds for processing.
20171022/140454.027 - U0029108 UCUDB: SQL_ERROR Database handles DB-HENV: 1194840 DB-HDBC: 119fa08
20171022/140454.027 - U0003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20171022/140454.027 - U0003592 UCUDB - Status: '' Native error: '60' Msg: 'ORA-00060: deadlock detected while waiting for resource'
20171022/140454.029 - U0003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '76:628.130.999'
20171022/140454.029 - U0029131 UCUDB - List of bind parameters:
20171022/140454.029 - U0029132 UCUDB: 'FIFO_Name = QUEUE.OAP.JACKAL'
20171022/140454.029 - U0003525 UCUDB: ===> 'UPDATE FIFO SET FIFO_Nr = FIFO_Nr + 1 WHERE FIFO_Name = ?'
20171022/140457.048 - U0029108 UCUDB: SQL_ERROR Database handles DB-HENV: 100f840 DB-HDBC: 101aa08
20171022/140457.048 - U0003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20171022/140457.048 - U0003592 UCUDB - Status: '' Native error: '60' Msg: 'ORA-00060: deadlock detected while waiting for resource'

When converting a job or workflow that is utilizing the Sync tab to a custom Queue with a limited "max. slot", the job or workflow running on the limited max. slot Queue causes a deadlock error within the Automation Engine's WP log files against the FIFO table.

Environment

OS Version: N/A

Cause

Cause type:
Defect
Root Cause: Due to not handling deadlocks correctly within the Automation Engine v10.

Resolution

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

Fix Status: Released

Fix Version(s):
Component(s): Automation Engine and Initial data

Automation Engine 12.1.0 - Available
Automation Engine 12.0.0 - Available
Automation Engine 11.2.0 - Available

Additional Information

Workaround :

1. Revert Queues with a limited "max. slot" back to utilizing Sync
2. Upgrade to Automation Engine V10 hotfix 10 -  Performance has been improved if a huge number of syncs are used. The handling of the deadlocks caused a high utilization of the RWP and led to delayed job-starts and has been fixed in this version.
3. Use Queues without any "max. slot" limit.

*It is recommended that item #2 be implemented if jobs & workflow have delays in starting while using a large number of syncs.