Wikimedia Bugzilla is closed!

Leaving a few words of feedback on an article results in 19 db write queries, wrapped in 3 transaction.  These result in incompatible row locks for the length of each transaction that will result in serialization at the transaction level.

Actual queries with transaction delimiters follow. This should speak for itself, but everything occurring in the second and third transactions needs to be eliminated, and the application logic behind this behavior redone.  Do not use mysql rows as counters that are updated every time someone leaves feedback, whether on the per-page basis, or especially the "all pages" afc_page_id = '0' aggregates.  Do not update rows by deleting them and reinserting with the desired value.  Deleting by secondary key essentially results in a table lock for the duration of the transaction.  Anything that is a rollup should be updated asynchronously in batches that combine and rollup writes.  

BEGIN

INSERT /* DatabaseBase::insert */  INTO `aft_article_feedback` (af_page_id,af_revision_id,af_created,af_user_id,af_user_ip,af_user_anon_token,af_form_id,af_experiment,af_link_id,af_has_comment) VALUES ('534366','506813755','20120813223135','14719981',NULL,'','6','M5_6','0','1')

INSERT /* ApiArticleFeedbackv5::saveUserRatings */  INTO `aft_article_answer` (aa_field_id,aa_response_rating,aa_response_text,aa_response_boolean,aa_response_option_id,aa_feedback_id,aat_id) VALUES ('16',NULL,NULL,'1',NULL,'253294',NULL),('17',NULL,'Well sourced article! (this is a test comment) ',NULL,NULL,'253294',NULL)

UPDATE /* ApiArticleFeedbackv5::saveUserRatings */  `aft_article_feedback` SET af_cta_id = '2' WHERE af_id = '253294'

INSERT /* ApiArticleFeedbackv5::saveUserProperties */  INTO `aft_article_feedback_properties` (afp_feedback_id,afp_key,afp_value_int) VALUES ('253294','contribs-lifetime','3'),('253294','contribs-6-months','0'),('253294','contribs-3-months','0'),('253294','contribs-1-months','0')

INSERT /* ApiArticleFeedbackv5::updateRollupRow */ IGNORE INTO `aft_article_revision_feedback_ratings_rollup` (afrr_page_id,afrr_revision_id,afrr_field_id,afrr_total,afrr_count) VALUES ('534366','506813755','16','0','0')

UPDATE /* ApiArticleFeedbackv5::updateRollupRow */  `aft_article_revision_feedback_ratings_rollup` SET afrr_total = afrr_total + 1,afrr_count = afrr_count + 1 WHERE afrr_page_id = '534366' AND afrr_revision_id = '506813755' AND afrr_field_id = '16'

COMMIT 

---

BEGIN

DELETE /* ApiArticleFeedbackv5::updateRollupRow */ FROM `aft_article_feedback_ratings_rollup` WHERE arr_page_id = '534366' AND arr_field_id = '16'

INSERT /* ApiArticleFeedbackv5::updateRollupRow */ IGNORE INTO `aft_article_feedback_ratings_rollup` (arr_page_id,arr_field_id,arr_total,arr_count) VALUES ('534366','16','9','42')

COMMIT

---

BEGIN

INSERT /* ApiArticleFeedbackv5Utils::updateFilterCounts */ IGNORE INTO `aft_article_filter_count` (afc_page_id,afc_filter_name,afc_filter_count) VALUES ('534366','visible','0'),('0','visible','0'),('534366','notdeleted','0'),('0','notdeleted','0'),('534366','all','0'),('0','all','0'),('534366','visible-comment','0'),('0','visible-comment','0'),('534366','visible-relevant','0'),('0','visible-relevant','0')

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '534366' AND afc_filter_name = 'visible'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '0' AND afc_filter_name = 'visible'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '534366' AND afc_filter_name = 'notdeleted'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '0' AND afc_filter_name = 'notdeleted'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '534366' AND afc_filter_name = 'all'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '0' AND afc_filter_name = 'all'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '534366' AND afc_filter_name = 'visible-comment'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '0' AND afc_filter_name = 'visible-comment'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '534366' AND afc_filter_name = 'visible-relevant'

UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */  `aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE afc_page_id = '0' AND afc_filter_name = 'visible-relevant'

COMMIT