Bug 1321336

Summary: Database deadlock while registering a new hosts in external oracle db
Product: Red Hat Satellite 5 Reporter: Stefan Nemeth <snemeth>
Component: RegistrationAssignee: Tomáš Kašpárek <tkasparek>
Status: CLOSED DUPLICATE QA Contact: Red Hat Satellite QA List <satqe-list>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 570CC: dyordano, ggainey, snemeth, tlestach
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2016-07-06 17:37:55 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Attachments:
Description Flags
db trace logs none

Description Stefan Nemeth 2016-03-25 15:45:36 UTC 
Description of problem:

Customer requested a bug report

Deadlock while registering system

UPDATE RHNPRIVATECHANNELFAMILY SET CURRENT_MEMBERS = ( RHN_CHANNEL.CHANNEL_FAMILY_CURRENT_MEMBERS(:B1 , :B2 )), FVE_CURRENT_MEMBERS = ( RHN_CHANNEL.CFAM_CURR_FVE_MEMBERS(:B1 , :B2 )) WHERE ORG_ID = :B2 AND CHANNEL_FAMILY_ID = :B1

was blocking

SELECT CREATED FROM RHNPRIVATECHANNELFAMILY WHERE CHANNEL_FAMILY_ID = :B2 AND ORG_ID = :B1 FOR UPDATE SELECT CREATED FROM RHNPRIVATECHANNELFAMILY WHERE CHANNEL_FAMILY_ID = :B2 AND ORG_ID = :B1 FOR UPDATE

Version-Release number of selected component (if applicable):


How reproducible:
100%

Steps to Reproduce:
1. on satellite 5.7 try to register host 
2.
3.

Actual results:

deadlock

Expected results:

server registered

Additional info:

workaround is to run a cron job hourly



BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNCHANNELFAMILY'             ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE);
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNCHANNELFAMILYMEMBERS'      ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE);
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNCHANNELFAMILYVIRTSUBLEVEL' ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNPRIVATECHANNELFAMILY'      ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSERVER'                    ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSERVERCHANNEL'             ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSERVERGROUP'               ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSERVERGROUPMEMBERS'        ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSERVERGROUPTYPE'           ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNSGTYPEVIRTSUBLEVEL'        ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
  SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'RHN' ,TabName  => 'RHNVIRTUALINSTANCE'           ,Estimate_Percent  => NULL ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate  => FALSE); 
END;
Comment 4 Grant Gainey 2016-05-18 20:44:15 UTC 
Is this a dup of BZ#1179770?
Comment 7 Stefan Nemeth 2016-06-17 09:48:22 UTC 
Created attachment 1169013 [details]
db trace logs
Comment 8 Grant Gainey 2016-07-06 17:37:55 UTC 
The register-, delete-, subscribe-, and entitle-system code paths can affect/lock the rhnserver, rhnservergroup, and rhnprivatechannelfamily tables in a variety of ways, depending on specific invocations. There several BZs reporting deadlock issues caused by locks on these tables being acquired in different orders, depending on how one gets into the codepaths in question. All of the current BZs (including this one) are being closed as DUPs of the oldest-currently-open-report, BZ#1179770. We will work to fix the problems under that BZ.

*** This bug has been marked as a duplicate of bug 1179770 ***