Bug 1480367 - [5.7] rhn_server.snapshot_server deadlock on Activation Key path
[5.7] rhn_server.snapshot_server deadlock on Activation Key path
Status: CLOSED ERRATA
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Server (Show other bugs)
570
Unspecified Unspecified
high Severity high
: ---
: ---
Assigned To: Jan Dobes
Jan Hutař
:
Depends On:
Blocks: sat5-deadlock 1481848
  Show dependency treegraph
 
Reported: 2017-08-10 16:11 EDT by Shannon Hughes
Modified: 2017-12-13 02:56 EST (History)
6 users (show)

See Also:
Fixed In Version: spacewalk-schema-2.3.2-35 satellite-schema-5.7.0.37-1 spacewalk-backend-2.3.3-55
Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
: 1481848 (view as bug list)
Environment:
Last Closed: 2017-12-13 02:56:42 EST
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)
Replacement for the postgresql lock_counts() proc (2.15 KB, text/x-vhdl)
2017-08-10 16:31 EDT, Grant Gainey
no flags Details

  None (edit)
Description Shannon Hughes 2017-08-10 16:11:55 EDT
Description of problem:

BZ to track the deadlock work that is occurring on the activation key and server snapshot path during registration: 

2017-08-09 15:35:30.511 EDT ERROR:  deadlock detected
2017-08-09 15:35:30.511 EDT DETAIL:  Process 12475 waits for ShareLock on transaction 949432; blocked by process 12488.
        Process 12488 waits for ShareLock on transaction 949435; blocked by process 12475.
        Process 12475: SELECT rhn_server.snapshot_server(1000012238, 'Package profile changed')
        Process 12488: SELECT rhn_entitlements.entitle_server(1000012239, 'enterprise_entitled')
2017-08-09 15:35:30.511 EDT HINT:  See server log for query details.
2017-08-09 15:35:30.511 EDT CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."rhnservergroup" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
        SQL statement "insert into rhnSnapshotServerGroup (snapshot_id, server_group_id) (
                        select        snapshot_id_v, sgm.server_group_id
                        from        rhnServerGroupMembers sgm
                        where        sgm.server_id = server_id_in
                )"
        PL/pgSQL function rhn_server.snapshot_server(numeric,character varying) line 37 at SQL statement
2017-08-09 15:35:30.511 EDT STATEMENT:  SELECT rhn_server.snapshot_server(1000012238, 'Package profile changed')


Turning off snapshots removes the deadlock but current work is looking at rhnServerSnapshopGroup within lock_counts()

Currently seeing on Satellite 5.7 but would expect this to affect 5.8 too.
Comment 1 Grant Gainey 2017-08-10 16:31 EDT
Created attachment 1311903 [details]
Replacement for the postgresql lock_counts() proc

After a lot of digging, it appears that the act of storing a server-snapshot is locking rows in rhnservergroup as a side-effect. This caused DB locks to be acquired out-of-order, and hence the deadlock.

This file adds the rhnSnapshotServerGroup to the list of locks managed by lock_counts(), and enforces lock-acquisition in the right order. It should address the deadlocks between snapshot_server() and entitle_server()

Apply by downloading lock_counts.sql onto the Sat5 instance, and running the foolowing command:

# spacewalk-sql --select-mode lock_counts.sql
Comment 17 errata-xmlrpc 2017-12-13 02:56:42 EST
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2017:3443

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