Bug 815964

+++ This bug was initially created as a clone of Bug #674071 +++

Description of problem:

When running a lot of monitoring probes with large tables (many data), RHN probes show a deadlock error message. 

Version-Release number of selected component (if applicable):
Red Hat Network Satellite 5.3 

How reproducible:
100%

Steps to Reproduce:
1. Create a lot of probes
2. Run
  
Actual results:

RHN Probes shows deadlock messages

Expected results:

RHN Probes run as expected

Additional info:

====== ISSUE 
Customer is continuing to see "deadlock detected" messages from Taskomatic:

***
INFO   | jvm 1    | 2011/01/14 08:17:03 | Caused by: 
INFO   | jvm 1    | 2011/01/14 08:17:03 | java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at "RHNSAT.RHN_SYNCH_PROBE_STATE", line 4
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at line 1
INFO   | jvm 1    | 2011/01/14 08:17:03 | 
INFO   | jvm 1    | 2011/01/14 08:17:03 |       ... 18 more
INFO   | jvm 1    | 2011/01/14 08:17:03 | 2011-01-14 08:17:03,145 [DefaultQuartzScheduler_Worker-0] ERROR com.redhat.rhn.taskomatic.core.SchedulerKernel - org.quartz.JobExecutionException: com.redhat.rhn.common.db.WrappedSQLException: ORA-00060: deadlock detected while waiting for resource
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at "RHNSAT.RHN_SYNCH_PROBE_STATE", line 4
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at line 1
INFO   | jvm 1    | 2011/01/14 08:17:03 |  [See nested exception: com.redhat.rhn.common.db.WrappedSQLException: ORA-00060: deadlock detected while waiting for resource
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at "RHNSAT.RHN_SYNCH_PROBE_STATE", line 4
INFO   | jvm 1    | 2011/01/14 08:17:03 | ORA-06512: at line 1


===== Analisys

Looking the oracle traces logs we could identify SQL which is causing the ORA-00060 deadlock. 

DEADLOCK DETECTED ( ORA-00060 )
  44   Current SQL Statement:
  45 
  46       UPDATE PROBE_STATE
  47       SET    LAST_CHECK = TO_DATE(:p1, 'YYYY-MM-DD HH24:MI:SS'),
  48              STATE = :p2,
  49              OUTPUT = :p3
  50       WHERE  SCOUT_ID = :p4
  51       AND    PROBE_ID = :p5
  52 
  53 End of information on OTHER waiting sessions.
  54 Current SQL statement for this session:
  55 UPDATE RHN_PROBE_STATE SET STATE = 'PENDING', OUTPUT = 'Awaiting update' WHERE LAST_CHECK < ( SELECT ( SYSDATE - GREATEST(15 / 60 / 24, ((3 * RHN_DEPLOYED_PROB     E.CHECK_INTERVAL_MINUTES) / 60 / 24))) FROM RHN_DEPLOYED_PROBE WHERE RHN_DEPLOYED_PROBE.RECID = RHN_PROBE_STATE.PROBE_ID )

========================================

Querying customer DB internally 

SQL> SELECT probe_type as "Probe Type", count(*) as "Total Probes" from rhn_probe GROUP BY probe_type;

Probe Type	Total Probes
--------------- ------------
check			3237
suite			 124

SQL>