Bug 964220 - queries causing blocking locks in prod
queries causing blocking locks in prod
Status: CLOSED EOL
Product: Red Hat Network
Classification: Red Hat
Component: RHN/Backend (Show other bugs)
AMS Backlog
Unspecified Unspecified
unspecified Severity unspecified
: ---
: ---
Assigned To: Nicky
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-05-17 10:59 EDT by Brooks
Modified: 2017-07-05 11:11 EDT (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Brooks 2013-05-17 10:59:32 EDT
Description of problem:

From problem reporter, Anilkumar Prodduturi in INC0039775
"

We have noticed some blocking locks in the RHN database today due to a combination of the following sql update statements.
DBA team had to clean some of the sessions to further not impact the rhn applications but please review the statements and see if we can avoid the blocking locks


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

The above sql seems to be coming from package rhn_channel.subscribe_server(:1,:2,:3); end;



UPDATE RHNPRIVATECHANNELFAMILY SET CURRENT_MEMBERS = CURRENT_MEMBERS -1 WHERE ORG_ID IN ( SELECT ORG_ID FROM RHNSERVER WHERE ID = :B1 ) AND CHANNEL_FAMILY_ID IN ( SELECT RCFM.CHANNEL_FAMILY_ID FROM RHNCHANNELFAMILYMEMBERS RCFM, RHNSERVERCHANNEL RSC WHERE RSC.SERVER_ID = :B1 AND RSC.CHANNEL_ID = RCFM.CHANNEL_ID AND NOT EXISTS ( SELECT 1 FROM RHNCHANNELFAMILYVIRTSUBLEVEL CFVSL, RHNSGTYPEVIRTSUBLEVEL SGTVSL, RHNSERVERENTITLEMENTVIEW SEV, RHNVIRTUALINSTANCE VI WHERE 1=1 AND VI.VIRTUAL_SYSTEM_ID = :B1 AND VI.HOST_SYSTEM_ID = SEV.SERVER_ID AND SEV.LABEL IN ('virtualization_host', 'virtualization_host_platform') AND SEV.SERVER_GROUP_TYPE_ID = SGTVSL.SERVER_GROUP_TYPE_ID AND SGTVSL.VIRT_SUB_LEVEL_ID = CFVSL.VIRT_SUB_LEVEL_ID AND CFVSL.CHANNEL_FAMILY_ID = RCFM.CHANNEL_FAMILY_ID ) )

The above sql seems to be coming from delete_server()


I guess there might be subscriptions and delete servers running against the same org id ... 
"

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

How reproducible:
Every time (in at least production)

Steps to Reproduce:
(Step 1 and 2 need to be completed Simultaneously to reproduce the problem)
1. register some systems to a channel from the command line
2. delete several systems through the UI which are subscribed to the same base channels as step 1.

(Problem Table: RHNPRIVATECHANNELFAMILY)
  
Actual results:
DB locks up in production database

Expected results:
No DB lock up should occur in production database

Additional info:

This was reported in INC0039775 by Anilkumar Prodduturi
Comment 1 Brooks 2013-05-17 11:00:16 EDT
US34246 added to AMS Backlog
Comment 2 Thomas "Shea" DeAntonio 2017-07-05 11:10:06 EDT
As you may already be aware, Red Hat is transitioning from the Red Hat Network (RHN) hosted interface to the Red Hat Subscription Management (RHSM) interface in July 2017. Red Hat has invested in and seen success from RHN for many years, and used that experience to build more robust technologies like RHSM. 
 
If you have not yet migrated your RHN systems to RHSM or you are a Red Hat Satellite customer, please review the information from. https://access.redhat.com/products/red-hat-subscription-management#migration. 
 
Since we are shutting down RHN, no further actions will be taken with this bug or enhancement request.  You can find additional information at RHSM product page (https://access.redhat.com/products/red-hat-subscription-management).  If you have further questions, please contact support (https://access.redhat.com/products/red-hat-subscription-management#support).
Comment 3 Thomas "Shea" DeAntonio 2017-07-05 11:11:39 EDT
As you may already be aware, Red Hat is transitioning from the Red Hat Network (RHN) hosted interface to the Red Hat Subscription Management (RHSM) interface in July 2017. Red Hat has invested in and seen success from RHN for many years, and used that experience to build more robust technologies like RHSM. 
 
If you have not yet migrated your RHN systems to RHSM or you are a Red Hat Satellite customer, please review the information from. https://access.redhat.com/products/red-hat-subscription-management#migration. 
 
Since we are shutting down RHN, no further actions will be taken with this bug or enhancement request.  You can find additional information at RHSM product page (https://access.redhat.com/products/red-hat-subscription-management).  If you have further questions, please contact support (https://access.redhat.com/products/red-hat-subscription-management#support).

Note You need to log in before you can comment on or make changes to this bug.