-1

we are using the below update query and it was making the row locking more than 3 mins. Please find the below query details.

update EmailReportSummary inner join EmailReports on EmailReportSummary.EMAIL_ID=EmailReports.EMAIL_ID set EmailReportSummary.OPENS_COUNT = IF(EmailReports.OPEN_COUNT = 0, EmailReportSummary.OPENS_COUNT+1,EmailReportSummary.OPENS_COUNT),EmailReports.OPENS=true,OPEN_COUNT = OPEN_COUNT+1,EmailReports.REPORT_MODIFIED_DATE=1532109134000 where EmailReports.EMAIL_REPORT_ID = 9823000147948239 and EmailReportSummary.CMP_CONTENT_ID = 9823000147491198

CREATE TABLE `EmailReportSummary` ( `SUMMARY_ID` bigint(19) NOT NULL DEFAULT '0', `EMAIL_ID` bigint(19) NOT NULL DEFAULT '0', `CMP_CONTENT_ID` bigint(19) NOT NULL DEFAULT '0', `OPENS_COUNT` int(10) DEFAULT '0', `CLICKS_COUNT` int(10) DEFAULT '0', `SCHEDULED_COUNT` int(10) DEFAULT '0', `SENT_COUNT` int(10) DEFAULT '0', `FORWARDS_COUNT` int(10) DEFAULT '0', `BOUNCE_COUNT` int(10) DEFAULT '0', `SPAM_COUNT` int(10) DEFAULT '0', `OPTOUT_COUNT` int(10) DEFAULT '0', `HARD_BOUNCE_COUNT` int(10) DEFAULT '0', `COMMENTS_COUNT` int(10) DEFAULT '0', `TRAP_COUNT` int(10) DEFAULT '0', `INVALID_DOMAIN_COUNT` int(10) DEFAULT '0', `BLOCKED_COUNT` int(10) DEFAULT '0', `IGNORED_COUNT` int(10) DEFAULT '0', `ARCHIVE_COUNT` int(10) DEFAULT '0', `REPLY_COUNT` int(10) DEFAULT '0', PRIMARY KEY (`SUMMARY_ID`), KEY `EmailReportSummary_FK1_IDX` (`EMAIL_ID`), KEY `EmailReportSummary_FK2_IDX` (`CMP_CONTENT_ID`), CONSTRAINT `EmailReportSummary_FK1` FOREIGN KEY (`EMAIL_ID`) REFERENCES `Email` (`EMAIL_ID`) ON DELETE CASCADE, CONSTRAINT `EmailReportSummary_FK2` FOREIGN KEY (`CMP_CONTENT_ID`) REFERENCES `EmailContentInfo` (`CMP_CONTENT_ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `EmailReports` ( `EMAIL_REPORT_ID` bigint(19) NOT NULL DEFAULT '0', `EMAIL_ID` bigint(19) NOT NULL DEFAULT '0', `SENT_DATE` bigint(19) DEFAULT NULL, `CONTACT_ID` bigint(19) NOT NULL DEFAULT '0', `REPORT_CREATED_DATE` bigint(19) NOT NULL DEFAULT '0', `REPORT_MODIFIED_DATE` bigint(19) DEFAULT NULL, `OPENS` tinyint(1) DEFAULT '0', `FORWARDS` tinyint(1) DEFAULT '0', `BOUNCE` int(10) DEFAULT '0', `BOUNCED_DATE` bigint(19) DEFAULT NULL, `SPAM` tinyint(1) DEFAULT '0', `OPT_OUT` tinyint(1) DEFAULT '0', `ZUID` bigint(19) NOT NULL DEFAULT '0', `UNIQUE_NO` varchar(100) DEFAULT NULL, `RECIPIENT_SELECTION_ID` bigint(19) DEFAULT NULL, `OPEN_COUNT` int(10) DEFAULT '0', `CLICK_COUNT` int(10) DEFAULT '0', `IGNORED` int(10) DEFAULT '0', `CMP_CONTENT_ID` bigint(19) DEFAULT NULL, `RESTRICT` int(10) DEFAULT '0', `TIME_ZONE` varchar(100) DEFAULT NULL, `GMT_DEVIATION` varchar(10) DEFAULT NULL, `CMP_COMMENTS_COUNT` int(10) DEFAULT '0', `IGNORED_BY_TRAP` int(10) DEFAULT '0', PRIMARY KEY (`EMAIL_REPORT_ID`), KEY `EmailReports_FK1_IDX` (`EMAIL_ID`), KEY `EmailReports_FK2_IDX` (`CONTACT_ID`), KEY `EmailReports_FK3_IDX` (`RECIPIENT_SELECTION_ID`), KEY `EmailReports_FK4_IDX` (`CMP_CONTENT_ID`), KEY `EmailReports_IDX1` (`SENT_DATE`), CONSTRAINT `EmailReports_FK1` FOREIGN KEY (`CAMPAIGN_EMAIL_ID`) REFERENCES `Email` (`EMAIL_ID`) ON DELETE CASCADE, CONSTRAINT `EmailReports_FK2` FOREIGN KEY (`CONTACT_ID`) REFERENCES `ContactInformation` (`CONTACT_ID`) ON DELETE CASCADE, CONSTRAINT `EmailReports_FK3` FOREIGN KEY (`RECIPIENT_SELECTION_ID`) REFERENCES `RecipientSelectionInfo` (`RECIPIENT_SELECTION_ID`) ON DELETE CASCADE, CONSTRAINT `EmailReports_FK4` FOREIGN KEY (`CMP_CONTENT_ID`) REFERENCES `EmailContentInfo` (`CMP_CONTENT_ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Arunkumar Muthuvel
  • 423
  • 3
  • 18

1 Answers1

0

Simple suggestion, try not to make an update statement so complex.

Maybe it doesn't cost so much time, but imagine what will happen when 100 requests try to update one same record, the last request will not get the row lock until all other requests finished.

A better way is to use code logic to replace 'update inner join'.

Mobility
  • 3,117
  • 18
  • 31