AWA: DEADLOCKS around SQL "MERGE INTO EH"

book

Article ID: 185783

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic Workload Automation - Automation Engine

Issue/Introduction

It is possible, if utilizing Max Parallel and Load Balancing in a HOSTG, to see DEADLOCKS or Time Critical Calls showing the following SQL:

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

 

Cause

This query can cause a deadlock in current design

Environment

Release : 12.x

Component : AUTOMATION ENGINE

Resolution

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

Fix version:
Component(s): Automation.Engine 

Automation.Engine 12.1.8

Automation.Engine 12.2.6

Automation Engine 12.3.2 HF1

Automation.Engine 12.3.3