Bug 1063821 - deadlocks in the database while moving systems from a channel to another
Summary: deadlocks in the database while moving systems from a channel to another
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Spacewalk
Classification: Community
Component: Server   
(Show other bugs)
Version: 2.1
Hardware: Unspecified
OS: Linux
medium
medium
Target Milestone: ---
Assignee: Milan Zázrivec
QA Contact: Red Hat Satellite QA List
URL:
Whiteboard:
Keywords: Reopened
Depends On:
Blocks: space21
TreeView+ depends on / blocked
 
Reported: 2014-02-11 13:35 UTC by Milan Zázrivec
Modified: 2014-03-04 13:08 UTC (History)
10 users (show)

Fixed In Version: spacewalk-schema-2.1.47-1
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: 1020965
Environment:
Last Closed: 2014-03-04 13:07:16 UTC
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)

Description Milan Zázrivec 2014-02-11 13:35:23 UTC
+++ This bug was initially created as a clone of Bug #1020965 +++

Description of problem:
after moving a set of 537 systems from one custom channel with
no errata to another custom channel with no errata, deadlocks
are raised and database space consumption raises exponentially

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

How reproducible:
At random

Steps to Reproduce:
1. sync in a custom channel, importing it from an external repository
2. change a set of systems from one channel to the other

Actual results:
Three types of deadlock show:
......................................
CEST ERROR:  deadlock detected
CEST DETAIL:  Process 24953 waits for ShareLock on transaction 37577; blocked by process 27459.
        Process 27459 waits for ShareLock on transaction 37227; blocked by process 24953.
        Process 24953: select * from rhn_channel.update_needed_cache($1) as result
        Process 27459: select * from rhn_channel.update_needed_cache($1) as result
......................................
CEST ERROR:  deadlock detected
CEST DETAIL:  Process 5455 waits for ShareLock on transaction 44101; blocked by process 27458.
        Process 27458 waits for ShareLock on transaction 43490; blocked by process 5455.
        Process 5455: select * from rhn_channel.subscribe_server($1, $2, 1, $3) as result
        Process 27458: select * from rhn_channel.update_needed_cache($1) as result
CEST HINT:  See server log for query details.
CEST CONTEXT:  SQL statement "UPDATE rhnServer SET channels_changed = current_timestamp WHERE id =  $1 "
        PL/pgSQL function "subscribe_server" line 95 at SQL statement
......................................
CEST ERROR:  deadlock detected
CEST DETAIL:  Process 22496 waits for ShareLock on transaction 50017; blocked by process 22509.
        Process 22509 waits for ShareLock on transaction 50004; blocked by process 22496.
        Process 22496: select * from rhn_channel.update_needed_cache($1) as result
        Process 22509: select * from rhn_channel.unsubscribe_server($1, $2) as result
CEST HINT:  See server log for query details.
CEST CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."rhnserver" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
        SQL statement "insert into rhnServerNeededCache (server_id, errata_id, package_id) (select distinct sp.server_id, x.errata_id, p.id FROM (SELECT sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr FROM rhnServerPackage sp_sp join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id GROUP BY sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id) sp join rhnPackage p ON p.name_id = sp.name_id join rhnPackageEvr pe ON pe.id = p.evr_id AND sp.max_evr < pe.evr join rhnPackageUpgradeArchCompat puac ON puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = p.package_arch_id join rhnServerChannel sc ON sc.server_id = sp.server_id join rhnChannelPackage cp ON cp.package_id = p.id AND cp.channel_id = sc.channel_id left join (SELECT ep.errata_id, ce.channel_id, ep.package_id FROM rhnChannelErrata ce join rhnErrataPackage ep ON ep.errata_id = ce.errata_id join rhnServerChannel sc_sc ON sc_sc.channel_id = ce.channel_id WHERE sc_sc.server_id =  $1 ) x ON x.channel_id = sc.channel_id AND x.package_id = cp.package_id where sp.server_id =  $1 )"
......................................

Expected results:
no deadlocks

Additional info:
N/A

Comment 1 Milan Zázrivec 2014-02-11 14:16:55 UTC
spacewalk.git master:

75b7f104508c05b6e8a5cb4533c8d8bc92893ddc
2706a514bf2343681cdcf1e9f9c855a827e8b088
79d339ffcd42e5e02b464097fea5be595fbe26c3
4cd36c6e4fbd7d431a19002fe190ccfea834d07f

Comment 2 Milan Zázrivec 2014-02-12 14:56:27 UTC
Re-worked fix: spacewalk.git master: 797782c1e260e773c4c0a3c24fbf356f517e98ad

Comment 3 Milan Zázrivec 2014-02-14 12:57:32 UTC
Additional fixes in spacewalk.git master:

8d0c918fbfb2ba8007afdc774d74cfaf229e9d70
3bf29925aa57d621c598da898ad4455987852eb6
f70df3b3bcda93204e0b5d7ab5912cabd62f8ac4

Comment 4 Milan Zázrivec 2014-02-17 13:26:57 UTC
Additional fix: spacewalk.git master: 10d98ef86325811bb9e51e17be24ce2bfbb22092

Comment 5 Milan Zázrivec 2014-02-18 13:36:35 UTC
spacewalk.git master: 062bebf04d5a627a4bcf646a58aa4f41d54eadb8

Comment 6 Matej Kollar 2014-03-04 13:07:16 UTC
Spacewalk 2.1 has been released.
https://fedorahosted.org/spacewalk/wiki/ReleaseNotes21

Comment 7 Matej Kollar 2014-03-04 13:08:45 UTC
Spacewalk 2.1 has been released.
https://fedorahosted.org/spacewalk/wiki/ReleaseNotes21


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