Oracle Deadlock Errors due to PRUNE_APM

Products

CA Application Performance Management Agent (APM / Wily / Introscope) INTROSCOPE

Issue/Introduction

Description:

Oracle Deadlock Errors reported in the alert log every morning:-

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/******/udump/******_ora_1162886.trc.

Taken the below from the trace files:-

   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:-

Deadlock graph:

                                                     ---------Blocker(s)--------                                     ---------Waiter(s)---------

Resource Name                         process
       session       holds       waits        process       session      holds       waits

TM-0000c2b3-00000000        31                190                SX                             20                 196             SS              SSX

TM-0000c2b8-00000000        20                196                SX                             31                 190             SX             SSX

session 190: DID 0001-001F-00000002     session 196: DID 0001-0014-000003D9

session 196: DID 0001-0014-000003D9     session 190: DID 0001-001F-00000002

Rows waited on:
Session 196: no row

Session 190: no row

Information on the OTHER waiting sessions:

Session 196:

  pid=20 serial=3099 audsid=44180 user: 53/APMUSER

  O/S info: user: wily, term: unknown, ospid: 1234, machine:  

            program: JDBC Thin Client

  application name: JDBC Thin Client, hash value=2546894660

Current SQL Statement:

DELETE FROM APM_VERTEX WHERE ID IN (SELECT ID FROM APM_VERTEX MINUS (SELECT DISTINCT(HEAD_VERTEX_ID) 
FROM APM_EDGE UNION SELECT DISTINCT(TAIL_VERTEX_ID) FROM APM_EDGE))

End of information on OTHER waiting sessions.

Current SQL statement for this session:

DELETE FROM APM_OWNER WHERE ID IN (SELECT ID FROM APM_OWNER MINUS (SELECT DISTINCT(OWNER_ID) 
FROM APM_EDGE UNION SELECT DISTINCT(HEAD_OWNER_ID) FROM APM_

EDGE UNION SELECT DISTINCT(TAIL_OWNER_ID) FROM APM_EDGE))

----- PL/SQL Call Stack -----

object      line  object

handle    number  name

43f851188         1  function APMUSER.PRUNE_APM_DATA

451a040a8         1  anonymous block

Solution:

Root cause:
The problem is that during running of prune_apm_data, the same process is invoked a second time from MOM, then a deadlock is detected and this transaction is aborted.
During investigation It was found that the execution of other stored proc started from another Introscope EM.

Solution:
Run the pruning process from different EMs and MoM at different time, you can control this using the below property available from the IntroscopeEnterpriseManager.properties.

Here is an example:

 	# This property controls how often the database is pruned of old data. 	
 	# It is formatted as an opensymphony quartz scheduler expression. 	
 	# This example prunes the database every minute, every hour: 	
 	#introscope.apm.pruning.cron.trigger.expression=0 0/1 0-23 * * ? 	
 	# By default, the prune task runs every day at 6 AM: 	
 	introscope.apm.pruning.cron.trigger.expression=0 0 6 * * ?

Environment

Release:
Component: APMISP