Type:
Bug
Priority:
Blocker
Resolution:
Fixed
Affects Version/s:
10.6.18 , 10.11.8 , 11.0.6 , 11.2.4 , 11.1.5 , 11.4.2 , 11.8.1
When running under innodb_snapshot_isolation=1, InnoDB gives error for conflicts that are not yet committed.
The following example shows this issue:
Note that if one removes the last SELECT statement or replaces that with an 'UPDATE SET b=3 where a=2', one will not get an error for the last update. Instead the last update would hang, which is what is expected.
--source include/have_innodb.inc
connect con1,localhost,root,,;
set @@innodb_snapshot_isolation=1;
connection default ;
set @@innodb_snapshot_isolation=1;
create table t1 (a int primary key auto_increment, b int ) engine=innodb;
insert into t1 values (1,1),(2,2);
begin ;
update t1 set b=3 where a=1;
connection con1;
begin ;
select * from t1;
update t1 set b=4 where a=1;
produces
mysqltest: At line 15: query 'update t1 set b=4 where a=1' failed: ER_CHECKREAD (1020): Record has changed since last read in table 't1'
Note that connection default has not done a commit or rollback yet.
What I would expect is that the last update should hang until default has done a commit or rollback.
This issue was reported by a user started to get ER_CHECKREAD errors when upgrading to 11.6.2.
I think that changing default of innodb_snapshot_isolation is the wrong thing to do for MariaDB as this will break any application that uses incremental updates (very typical in data warehouse applications) to increase performance:
UPDATE warehouse set inventory=inventory-1 where item="pc";
innodb_snapshot_isolation is a good thing to have, but probably not for the current MariaDB user base.
{"report":{"fcp":902.5,"ttfb":307.2000000476837,"pageVisibility":"visible","entityId":133921,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"a0f52b3c-da4f-4d67-ac99-9e8b04cf26e1","navigationType":0,"readyForUser":967.2000000476837,"redirectCount":0,"resourceLoadedEnd":1145,"resourceLoadedStart":331,"resourceTiming":[{"duration":142.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":331,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":331,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":473.5,"responseStart":0,"secureConnectionStart":0},{"duration":142.30000007152557,"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":331.39999997615814,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":331.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":473.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":151.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":331.60000002384186,"connectEnd":331.60000002384186,"connectStart":331.60000002384186,"domainLookupEnd":331.60000002384186,"domainLookupStart":331.60000002384186,"fetchStart":331.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":331.60000002384186,"responseEnd":483.3000000715256,"responseStart":483.3000000715256,"secureConnectionStart":331.60000002384186},{"duration":215.70000004768372,"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":331.89999997615814,"connectEnd":331.89999997615814,"connectStart":331.89999997615814,"domainLookupEnd":331.89999997615814,"domainLookupStart":331.89999997615814,"fetchStart":331.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":331.89999997615814,"responseEnd":547.6000000238419,"responseStart":547.6000000238419,"secureConnectionStart":331.89999997615814},{"duration":219.29999995231628,"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":332.10000002384186,"connectEnd":332.10000002384186,"connectStart":332.10000002384186,"domainLookupEnd":332.10000002384186,"domainLookupStart":332.10000002384186,"fetchStart":332.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":332.10000002384186,"responseEnd":551.3999999761581,"responseStart":551.3999999761581,"secureConnectionStart":332.10000002384186},{"duration":219.69999992847443,"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":332.2000000476837,"connectEnd":332.2000000476837,"connectStart":332.2000000476837,"domainLookupEnd":332.2000000476837,"domainLookupStart":332.2000000476837,"fetchStart":332.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":332.2000000476837,"responseEnd":551.8999999761581,"responseStart":551.8999999761581,"secureConnectionStart":332.2000000476837},{"duration":219.80000007152557,"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":332.39999997615814,"connectEnd":332.39999997615814,"connectStart":332.39999997615814,"domainLookupEnd":332.39999997615814,"domainLookupStart":332.39999997615814,"fetchStart":332.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":332.39999997615814,"responseEnd":552.2000000476837,"responseStart":552.2000000476837,"secureConnectionStart":332.39999997615814},{"duration":219.79999995231628,"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":332.8000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":332.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":552.6000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":219.80000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":332.89999997615814,"connectEnd":332.89999997615814,"connectStart":332.89999997615814,"domainLookupEnd":332.89999997615814,"domainLookupStart":332.89999997615814,"fetchStart":332.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":332.89999997615814,"responseEnd":552.7000000476837,"responseStart":552.7000000476837,"secureConnectionStart":332.89999997615814},{"duration":220.10000002384186,"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":333,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":333,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":553.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":220.10000002384186,"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":333.10000002384186,"connectEnd":333.10000002384186,"connectStart":333.10000002384186,"domainLookupEnd":333.10000002384186,"domainLookupStart":333.10000002384186,"fetchStart":333.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":333.10000002384186,"responseEnd":553.2000000476837,"responseStart":553.2000000476837,"secureConnectionStart":333.10000002384186},{"duration":405.89999997615814,"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":391.8000000715256,"connectEnd":391.8000000715256,"connectStart":391.8000000715256,"domainLookupEnd":391.8000000715256,"domainLookupStart":391.8000000715256,"fetchStart":391.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":391.8000000715256,"responseEnd":797.7000000476837,"responseStart":797.7000000476837,"secureConnectionStart":391.8000000715256},{"duration":739.8999999761581,"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":405.10000002384186,"connectEnd":405.10000002384186,"connectStart":405.10000002384186,"domainLookupEnd":405.10000002384186,"domainLookupStart":405.10000002384186,"fetchStart":405.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":405.10000002384186,"responseEnd":1145,"responseStart":1145,"secureConnectionStart":405.10000002384186},{"duration":399.10000002384186,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":699.3999999761581,"connectEnd":699.3999999761581,"connectStart":699.3999999761581,"domainLookupEnd":699.3999999761581,"domainLookupStart":699.3999999761581,"fetchStart":699.3999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":699.3999999761581,"responseEnd":1098.5,"responseStart":1098.5,"secureConnectionStart":699.3999999761581},{"duration":276.60000002384186,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":880.1000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":880.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1156.7000000476837,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":175,"responseStart":307,"responseEnd":405,"domLoading":310,"domInteractive":1167,"domContentLoadedEventStart":1167,"domContentLoadedEventEnd":1204,"domComplete":2464,"loadEventStart":2464,"loadEventEnd":2466,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1149.5},{"name":"bigPipe.sidebar-id.end","time":1150.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1150.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":1153.2000000476837},{"name":"activityTabFullyLoaded","time":1210.8000000715256}],"measures":[],"correlationId":"3292a868bca82c","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":70,"dbReadsTimeInMs":14,"dbConnsTimeInMs":20,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}