Type:
Priority:
Resolution:
Fixed
Affects Version/s:
10.6
After the changes made in the scope of MDEV-515
--source include/have_innodb.inc
create table t1 (pk int primary key ) engine=InnoDB;
start transaction ;
insert into t1 values (1);
--connect (con1,localhost,root,,)
insert into t1 values (2);
The second INSERT, which in earlier versions is instant, will now hang until the first connection finishes the transaction (or will time out if it doesn't happen within the timeout interval).
While the locking change was clearly intentional and explicitly mentioned at least in the commit comment to MDEV-515 MDEV-515
The scope of MDEV-515
On the other hand, "create/empty a table and start using it concurrently" scenario above is as basic as it gets; and the loss can be not only in performance (in case the first transaction is reasonably fast and other ones are just delayed), but in more extreme cases, when the first transaction remains open for a long time, it can render the application non-functional – everything else will be failing with timeouts.
The problem can be happening anywhere within an application, both in business logic and deep in maintenance/service jobs.
It will represent as seemingly sporadic not reproducible weird delays and timeouts and will be nearly impossible to investigate – and even if the cause happens to be revealed, changing this logic in all existing applications won't be realistic.
I don't think that even a possibility to "opt-out" the new behavior would be sufficient, as users are generally not aware of details of the internal implementation of applications they use, as well as database admins if, for example, they maintain a shared hosting.
blocks
MDEV-24993
pymysql - executemany test failing - lock timeout - insert wait on non-indexed table
Closed
causes
MDEV-25297
InnoDB: Failing assertion: trx->roll_limit <= trx->undo_no in trx_rollback_start
Closed
MDEV-25315
Assertion `ptr' failed in ut_align_down | SIGSEGV in dict_table_t::not_redundant | Assertion `!lock_sys_t::get_first(receiver_cell, receiver_id, receiver_heap_no)' failed in lock_rec_move
Closed
MDEV-25401
Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed in Diagnostics_area::set_ok_status on SAVEPOINT
Closed
MDEV-25496
Assertion `trx->bulk_insert' failed in trx_undo_report_row_operation on INSERT
Closed
MDEV-25534
Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock on INSERT
Closed
MDEV-26121
[Note] InnoDB: Resetting invalid page [page id: space=5, page number=3] type 17855 to 6.
Closed
is caused by
MDEV-515
innodb bulk insert
Closed
relates to
MDEV-24859
Assertion `static_cast<ins_node_t*>(thr->run_node)->bulk_insert' failed in trx_undo_report_row_operation
Closed
MDEV-24914
Assertion `lock_rec_get_first( lock_hash, receiver, receiver_heap_no) == __null || lock_hash == &lock_sys.prdt_hash || lock_hash == &lock_sys.prdt_page_hash' failed in lock_rec_move_low
Closed
MDEV-25036
use bulk insert optimization for multiple insert statements
Open
MDEV-28327
InnoDB persistent statistics fail to update after bulk insert
Closed
MDEV-29545
InnoDB replace statement returns "can't find record" error during bulk insert operation.
Closed
MDEV-30063
InnoDB: Failing assertion: ib_table->stat_initialized in ha_innobase::info_low()
Closed
MDEV-30173
Document magic 10.6+ spell for faster data loading
Open
MDEV-31985
IGNORE in INSERT and LOAD are being ignored in InnoDB bulk insert
Confirmed
MDEV-31835
LOAD DATA into InnoDB w/partitions: huge performance loss, affected 10.6+
Closed
MDEV-32250
To benefit from MDEV-515 , please make option --no-autocommit default in mysqldump from 10.6 onwards
Closed
mentioned in
Page Loading...
Show 14 more links (2 causes, 1 is caused by, 10 relates to, 1 mentioned in)
made changes -
2021-02-24 15:51
Priority
Major
[ 3
]
Blocker
[ 1
]
made changes -
2021-02-26 16:27
Assignee
Sergei Golubchik
[ serg
]
Marko Mäkelä
[ marko
]
made changes -
2021-03-03 13:39
Status
Open
[ 1
]
In Progress
[ 3
]
made changes -
2021-03-16 13:26
issue.field.resolutiondate
2021-03-16 13:26:33.0
2021-03-16 13:26:33.811
made changes -
2021-03-16 13:26
Fix Version/s
10.6.0
[ 24431
]
Fix Version/s
10.6
[ 24028
]
Resolution
Fixed
[ 1
]
Status
In Progress
[ 3
]
Closed
[ 6
]
made changes -
2021-12-06 21:52
Workflow
MariaDB v3
[ 118960
]
MariaDB v4
[ 158873
]
made changes -
2022-09-27 20:26
Remote Link
This issue links to "Page (MariaDB Confluence)"
[ 34499
]
{"report":{"fcp":2104.0999999046326,"ttfb":629.3999998569489,"pageVisibility":"visible","entityId":96749,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":32,"apdex":0.5,"journeyId":"394540dd-7c6f-448e-9129-fd6bcd4237dc","navigationType":0,"readyForUser":2199.0999999046326,"redirectCount":0,"resourceLoadedEnd":2649.399999856949,"resourceLoadedStart":636.3999998569489,"resourceTiming":[{"duration":926.1000001430511,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":636.3999998569489,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":636.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1562.5,"responseStart":0,"secureConnectionStart":0},{"duration":926,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":636.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":636.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1562.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":961.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":636.8999998569489,"connectEnd":636.8999998569489,"connectStart":636.8999998569489,"domainLookupEnd":636.8999998569489,"domainLookupStart":636.8999998569489,"fetchStart":636.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":636.8999998569489,"responseEnd":1598.0999999046326,"responseStart":1598.0999999046326,"secureConnectionStart":636.8999998569489},{"duration":1044.4000000953674,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":637.0999999046326,"connectEnd":637.0999999046326,"connectStart":637.0999999046326,"domainLookupEnd":637.0999999046326,"domainLookupStart":637.0999999046326,"fetchStart":637.0999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":637.0999999046326,"responseEnd":1681.5,"responseStart":1681.5,"secureConnectionStart":637.0999999046326},{"duration":1048.3000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":637.3999998569489,"connectEnd":637.3999998569489,"connectStart":637.3999998569489,"domainLookupEnd":637.3999998569489,"domainLookupStart":637.3999998569489,"fetchStart":637.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":637.3999998569489,"responseEnd":1685.7000000476837,"responseStart":1685.7000000476837,"secureConnectionStart":637.3999998569489},{"duration":1048.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":637.5999999046326,"connectEnd":637.5999999046326,"connectStart":637.5999999046326,"domainLookupEnd":637.5999999046326,"domainLookupStart":637.5999999046326,"fetchStart":637.5999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":637.5999999046326,"responseEnd":1686.0999999046326,"responseStart":1686.0999999046326,"secureConnectionStart":637.5999999046326},{"duration":1049,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":637.7000000476837,"connectEnd":637.7000000476837,"connectStart":637.7000000476837,"domainLookupEnd":637.7000000476837,"domainLookupStart":637.7000000476837,"fetchStart":637.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":637.7000000476837,"responseEnd":1686.7000000476837,"responseStart":1686.7000000476837,"secureConnectionStart":637.7000000476837},{"duration":1141.4000000953674,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":637.8999998569489,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":637.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1779.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":1049.0999999046326,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":638,"connectEnd":638,"connectStart":638,"domainLookupEnd":638,"domainLookupStart":638,"fetchStart":638,"redirectEnd":0,"redirectStart":0,"requestStart":638,"responseEnd":1687.0999999046326,"responseStart":1687.0999999046326,"secureConnectionStart":638},{"duration":1141.2999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/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":638.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":638.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1779.5,"responseStart":0,"secureConnectionStart":0},{"duration":1049.4000000953674,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/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":638.2999999523163,"connectEnd":638.2999999523163,"connectStart":638.2999999523163,"domainLookupEnd":638.2999999523163,"domainLookupStart":638.2999999523163,"fetchStart":638.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":638.2999999523163,"responseEnd":1687.7000000476837,"responseStart":1687.7000000476837,"secureConnectionStart":638.2999999523163},{"duration":1865.5999999046326,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":640.2999999523163,"connectEnd":640.2999999523163,"connectStart":640.2999999523163,"domainLookupEnd":640.2999999523163,"domainLookupStart":640.2999999523163,"fetchStart":640.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":640.2999999523163,"responseEnd":2505.899999856949,"responseStart":2505.899999856949,"secureConnectionStart":640.2999999523163},{"duration":2009,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":640.3999998569489,"connectEnd":640.3999998569489,"connectStart":640.3999998569489,"domainLookupEnd":640.3999998569489,"domainLookupStart":640.3999998569489,"fetchStart":640.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":640.3999998569489,"responseEnd":2649.399999856949,"responseStart":2649.399999856949,"secureConnectionStart":640.3999998569489},{"duration":714.9000000953674,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1791.3999998569489,"connectEnd":1791.3999998569489,"connectStart":1791.3999998569489,"domainLookupEnd":1791.3999998569489,"domainLookupStart":1791.3999998569489,"fetchStart":1791.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":1791.3999998569489,"responseEnd":2506.2999999523163,"responseStart":2506.2999999523163,"secureConnectionStart":1791.3999998569489},{"duration":595.3999998569489,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":2098,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":2098,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2693.399999856949,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":341,"responseStart":629,"responseEnd":632,"domLoading":633,"domInteractive":2696,"domContentLoadedEventStart":2696,"domContentLoadedEventEnd":2756,"domComplete":3219,"loadEventStart":3220,"loadEventEnd":3220,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2656.5999999046326},{"name":"bigPipe.sidebar-id.end","time":2657.399999856949},{"name":"bigPipe.activity-panel-pipe-id.start","time":2657.5999999046326},{"name":"bigPipe.activity-panel-pipe-id.end","time":2663.0999999046326},{"name":"activityTabFullyLoaded","time":2778.5}],"measures":[],"correlationId":"b6a6aff0346fa9","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":225,"dbReadsTimeInMs":61,"dbConnsTimeInMs":76,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Blocker
MDEV-515 innodb bulk insert
Page Loading...