Deadlock between two WPs doing an update on EH and select for update on AH
Article ID: 387190
Updated On: 03-14-2025
Products
Issue/Introduction
Some deadlocks are produced in the Automation Engine Database (Oracle for the example below but could also appear on any other type) continously ocurring at a particular time (7am) between two WP sessions when launching some particular Jobs SQL.
The relevant messages in the wp log are the following:
20240718/070018.551 - UPDATE EH SET EH_ParentHir = ?, EH_ParentPrc = ? WHERE EH_AH_Idnr in (SELECT EH_AH_Idnr from EH WHERE EH_ParentPrc = ? AND EH_Status < ?)
20240718/070023.785 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: abc6c000 DB-HDBC: abc8a198
20240718/070023.785 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20240718/070023.785 - U00003592 UCUDB - Status: '' Native error: '60' Msg: 'ORA-00060: deadlock detected while waiting for resource'
20240718/070023.785 - DEADLOCK: rollback
20240718/070023.785 - DEADLOCK: rollback->0
20240718/070027.488 - DEADLOCK: sleep->3686
20240718/070027.488 - U00000006 DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.
20240718/070027.488 - U00003594 UCUDB Ret: '6' opcode: 'EXEC' SQL Stmnt: 'UPDATE EH SET EH_ParentHir = ?, EH_ParentPrc = ? WHERE EH_AH_Idnr in (SELECT EH_AH_Idnr from EH WHERE EH_ParentPrc = ? AND EH_Status < ?)'
20240718/070027.488 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '8:932.492.200'
When analyzing the related alert trace file generated at Oracle DB:
Extract from latest Oracle trc:
*** 2024-07-18T07:00:21.557174+02:00
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors
[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:
Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TX-0005000B-003D4607-9858B539-00000000 123 643 X 27159 127 646 X 60239
TX-00010019-00322C26-9858B539-00000000 127 646 X 60239 123 643 X 27159
----- Information for waiting sessions -----
Session 643:
Holds resource TX-0005000B-003D4607-9858B539-00000000 acquired 3 seconds ago.
sid: 643 ser: 27159 audsid: 106887781 user: 111/DATABASEUSER
application name: UCsrvwp.exe, hash value=1961885778
current SQL:
UPDATE EH SET EH_ParentHir = :A0001, EH_ParentPrc = :A0002 WHERE EH_AH_Idnr in (SELECT EH_AH_Idnr from EH WHERE EH_ParentPrc = :A0003 AND EH_Status < :A0004)
Session 646:
Holds resource TX-00010019-00322C26-9858B539-00000000 acquired 3 seconds ago.
sid: 646 ser: 60239 audsid: 106887783 user: 111/DATABASEUSER
application name: UCsrvwp.exe, hash value=1961885778
current SQL:
select AH_Client, AH_OType, AH_TimeStamp4,ROWID from AH where AH_Idnr = :A0001 FOR UPDATE
Environment
Automation Engine 21.x and 24.x
Cause
Defect
Resolution
Update to a fix version listed below or a newer version if available.
Fix version:
Component(s): Automation Engine
Automation.Engine 21.0.13 - Available
Automation.Engine 24.3.0 - Available
Additional Information
Defect ID: DE129828
Defect Public Title: Database access logic has been improved to avoid SQL DEADLOCK messages.
Defect Public Description: To avoid SQL DEADLOCK, create a message race condition. If the status is "eh-status-changing-log", it means jpexec-msg-chprdlog message is already sent once but not processed, do not send any more messages.
Feedback
