Type:
Bug
Priority:
Major
Resolution:
Fixed
Affects Version/s:
5.5(EOL) , 10.0(EOL) , 10.1(EOL) , 10.2(EOL) , 10.3(EOL) , 10.4(EOL) , 10.5 , 10.6
On transaction rollback, we are unnecessarily waiting for redo log to complete. If the server were killed, the transaction would be rolled back just fine. If we are lucky, the changes of the transaction were never written to redo log, and recovery would never even know about the transaction. This is about a minor tweak to the transaction commit code:
diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc
index 0f735faacfa..f24e44d921a 100644
--- a/storage/innobase/trx/trx0trx.cc
+++ b/storage/innobase/trx/trx0trx.cc
@@ -1347,7 +1347,7 @@ inline void trx_t::commit_in_memory(const mtr_t *mtr)
serialize all commits and prevent a group of transactions from
gathering. */
- commit_lsn= mtr->commit_lsn();
+ commit_lsn= undo_no ? mtr->commit_lsn() : 0;
if (!commit_lsn)
/* Nothing to be done. */;
else if (flush_log_later)
This code is also invoked at the end of rollback. The trx_t::undo_no is the number of rows that would remain in the undo log. After a full rollback, that number would always be 0.
A simple benchmark shows the difference:
create table t(id int primary key )engine=innodb;
delimiter $$
create procedure ir(n INT )
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
START TRANSACTION ; INSERT INTO t SET id=0; ROLLBACK ;
SET i:=i+1;
UNTIL i>n END REPEAT;
END $$
delimiter ;
call ir(10000);
On my system, with innodb_flush_log_at_trx_commit=1 , the test before the patch takes 7.6 seconds. With the patch, it is only 2.3 seconds.
relates to
MDEV-24341
Innodb - do not block in foreground thread in log_write_up_to()
Closed
There are no comments yet on this issue.
{"report":{"fcp":757.2000007629395,"ttfb":199.9000005722046,"pageVisibility":"visible","entityId":100780,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"0bf0ad6f-1601-4d6c-b804-3b86839f2099","navigationType":0,"readyForUser":820.2000007629395,"redirectCount":0,"resourceLoadedEnd":511.4000005722046,"resourceLoadedStart":205.70000076293945,"resourceTiming":[{"duration":5.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":205.70000076293945,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":205.70000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":211.20000076293945,"responseStart":0,"secureConnectionStart":0},{"duration":5.800000190734863,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":206,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":206,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":211.80000019073486,"responseStart":0,"secureConnectionStart":0},{"duration":108,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":206.20000076293945,"connectEnd":206.20000076293945,"connectStart":206.20000076293945,"domainLookupEnd":206.20000076293945,"domainLookupStart":206.20000076293945,"fetchStart":206.20000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":216.10000038146973,"responseEnd":314.20000076293945,"responseStart":239.5,"secureConnectionStart":206.20000076293945},{"duration":139.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":206.30000019073486,"connectEnd":206.30000019073486,"connectStart":206.30000019073486,"domainLookupEnd":206.30000019073486,"domainLookupStart":206.30000019073486,"fetchStart":206.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":216.5,"responseEnd":345.80000019073486,"responseStart":242.30000019073486,"secureConnectionStart":206.30000019073486},{"duration":33.09999942779541,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":206.4000005722046,"connectEnd":206.4000005722046,"connectStart":206.4000005722046,"domainLookupEnd":206.4000005722046,"domainLookupStart":206.4000005722046,"fetchStart":206.4000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":217.60000038146973,"responseEnd":239.5,"responseStart":238.30000019073486,"secureConnectionStart":206.4000005722046},{"duration":31.800000190734863,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":206.5,"connectEnd":206.5,"connectStart":206.5,"domainLookupEnd":206.5,"domainLookupStart":206.5,"fetchStart":206.5,"redirectEnd":0,"redirectStart":0,"requestStart":218.10000038146973,"responseEnd":238.30000019073486,"responseStart":236.9000005722046,"secureConnectionStart":206.5},{"duration":45,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":206.60000038146973,"connectEnd":206.60000038146973,"connectStart":206.60000038146973,"domainLookupEnd":206.60000038146973,"domainLookupStart":206.60000038146973,"fetchStart":206.60000038146973,"redirectEnd":0,"redirectStart":0,"requestStart":220,"responseEnd":251.60000038146973,"responseStart":250.5,"secureConnectionStart":206.60000038146973},{"duration":11.199999809265137,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":206.70000076293945,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":206.70000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":217.9000005722046,"responseStart":0,"secureConnectionStart":0},{"duration":13.09999942779541,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":206.9000005722046,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":206.9000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":220,"responseStart":0,"secureConnectionStart":0},{"duration":43.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":206.9000005722046,"connectEnd":206.9000005722046,"connectStart":206.9000005722046,"domainLookupEnd":206.9000005722046,"domainLookupStart":206.9000005722046,"fetchStart":206.9000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":220.10000038146973,"responseEnd":250.4000005722046,"responseStart":249.30000019073486,"secureConnectionStart":206.9000005722046},{"duration":66.20000076293945,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":207,"connectEnd":207,"connectStart":207,"domainLookupEnd":207,"domainLookupStart":207,"fetchStart":207,"redirectEnd":0,"redirectStart":0,"requestStart":222.30000019073486,"responseEnd":273.20000076293945,"responseStart":272.1000003814697,"secureConnectionStart":207},{"duration":30.299999237060547,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":216.20000076293945,"connectEnd":230.9000005722046,"connectStart":230.9000005722046,"domainLookupEnd":230.9000005722046,"domainLookupStart":230.9000005722046,"fetchStart":216.20000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":231.10000038146973,"responseEnd":246.5,"responseStart":244.4000005722046,"secureConnectionStart":230.9000005722046},{"duration":294.4000005722046,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":217,"connectEnd":217,"connectStart":217,"domainLookupEnd":217,"domainLookupStart":217,"fetchStart":217,"redirectEnd":0,"redirectStart":0,"requestStart":243.9000005722046,"responseEnd":511.4000005722046,"responseStart":508.5,"secureConnectionStart":217},{"duration":202.20000076293945,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":524,"connectEnd":524,"connectStart":524,"domainLookupEnd":524,"domainLookupStart":524,"fetchStart":524,"redirectEnd":0,"redirectStart":0,"requestStart":695.6000003814697,"responseEnd":726.2000007629395,"responseStart":725,"secureConnectionStart":524},{"duration":149.29999923706055,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":734.2000007629395,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":734.2000007629395,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":883.5,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":73,"responseStart":200,"responseEnd":217,"domLoading":204,"domInteractive":872,"domContentLoadedEventStart":872,"domContentLoadedEventEnd":907,"domComplete":1203,"loadEventStart":1203,"loadEventEnd":1204,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":857},{"name":"bigPipe.sidebar-id.end","time":857.7000007629395},{"name":"bigPipe.activity-panel-pipe-id.start","time":857.9000005722046},{"name":"bigPipe.activity-panel-pipe-id.end","time":858.4000005722046},{"name":"activityTabFullyLoaded","time":915.7000007629395}],"measures":[],"correlationId":"685567965a88aa","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":71,"dbReadsTimeInMs":9,"dbConnsTimeInMs":15,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}