Type:
Technical task
Priority:
Major
Resolution:
Fixed
Affects Version/s:
None
Component/s:
None
If CREATE OR REPLACE, executed under lock, fails in the process, it keeps MDL_EXCLUSIVE lock on the table it attempted to re-create. It might happen both when the drop part fails, or when the create part fails (in the latter case, the connection holds a lock for a non-existing table).
After it happens, the connection itself does not realize it keeps the lock, and cannot use it any longer.
UNLOCK solves the problem, so it's not critical.
Example 1: failure upon creation.
We lock the table, attempt to re-create it, it gets dropped but not created; we still have MDL_EXCLUSIVE for it, but cannot create it until UNLOCK.
MariaDB [db]> create table t1 (i int );
Query OK, 0 rows affected (0.59 sec)
MariaDB [db]> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db]> select * from information_schema.metadata_lock_info;
+ -----------+--------------------------+---------------+----------------------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+ -----------+--------------------------+---------------+----------------------+--------------+------------+
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
| 5 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT | Table metadata lock | db | t1 |
+ -----------+--------------------------+---------------+----------------------+--------------+------------+
3 rows in set (0.00 sec)
MariaDB [db]> create or replace table t1;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [db]> show tables;
Empty set (0.00 sec)
MariaDB [db]> select * from information_schema.metadata_lock_info;
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
| 5 | MDL_EXCLUSIVE | MDL_EXPLICIT | Table metadata lock | db | t1 |
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
3 rows in set (0.00 sec)
MariaDB [db]> create table t1 (i int );
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
MariaDB [db]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db]> select * from information_schema.metadata_lock_info;
Empty set (0.00 sec)
Example 2: failure upon drop.
We lock the table, attempt to re-create it, it does not get dropped; we still have MDL_EXCLUSIVE for it, but cannot read from it.
MariaDB [db]> create table t1 (a int , index (a)) engine=InnoDB;
Query OK, 0 rows affected (0.98 sec)
MariaDB [db]> create table t2 (b int , foreign key (b) references t1(a) on delete cascade ) engine=InnoDB;
Query OK, 0 rows affected (0.79 sec)
MariaDB [db]> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [db]> insert into t2 values (1),(2);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [db]> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db]> create or replace table t1 (a int , index (a)) engine=InnoDB;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [db]> show tables;
+ --------------+
| Tables_in_db |
+ --------------+
| t1 |
| t2 |
+ --------------+
2 rows in set (0.00 sec)
MariaDB [db]> select * from information_schema.metadata_lock_info;
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
| 5 | MDL_EXCLUSIVE | MDL_EXPLICIT | Table metadata lock | db | t1 |
+ -----------+-------------------------+---------------+----------------------+--------------+------------+
3 rows in set (0.00 sec)
MariaDB [db]> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
revision-id: monty@askmonty.org-20140205193616-7vcabbq3nrpsvpjj
revno: 3981
branch-nick: 10.0-monty
{"report":{"fcp":1373,"ttfb":496.09999999962747,"pageVisibility":"visible","entityId":30404,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"a65bb9af-ce13-4f30-b3ff-6199c783388a","navigationType":0,"readyForUser":1474.800000000745,"redirectCount":0,"resourceLoadedEnd":1903.5,"resourceLoadedStart":501.90000000037253,"resourceTiming":[{"duration":377,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":501.90000000037253,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":501.90000000037253,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":878.9000000003725,"responseStart":0,"secureConnectionStart":0},{"duration":377.2000000011176,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/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":502.09999999962747,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":502.09999999962747,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":879.3000000007451,"responseStart":0,"secureConnectionStart":0},{"duration":385.09999999962747,"initiatorType":"script","name":"https://jira.mariadb.org/s/e9b27a47da5fb0f74a35acd57e9847fb-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":502.30000000074506,"connectEnd":502.30000000074506,"connectStart":502.30000000074506,"domainLookupEnd":502.30000000074506,"domainLookupStart":502.30000000074506,"fetchStart":502.30000000074506,"redirectEnd":0,"redirectStart":0,"requestStart":502.30000000074506,"responseEnd":887.4000000003725,"responseStart":887.3000000007451,"secureConnectionStart":502.30000000074506},{"duration":470.59999999962747,"initiatorType":"script","name":"https://jira.mariadb.org/s/c32eb0da7ad9831253f8397e6cc26afd-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/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":502.5,"connectEnd":502.5,"connectStart":502.5,"domainLookupEnd":502.5,"domainLookupStart":502.5,"fetchStart":502.5,"redirectEnd":0,"redirectStart":0,"requestStart":502.5,"responseEnd":973.0999999996275,"responseStart":973.0999999996275,"secureConnectionStart":502.5},{"duration":474.40000000037253,"initiatorType":"script","name":"https://jira.mariadb.org/s/bc0bcb146314416123c992714ee00ff7-CDN/lu2bv2/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":502.69999999925494,"connectEnd":502.69999999925494,"connectStart":502.69999999925494,"domainLookupEnd":502.69999999925494,"domainLookupStart":502.69999999925494,"fetchStart":502.69999999925494,"redirectEnd":0,"redirectStart":0,"requestStart":502.69999999925494,"responseEnd":977.0999999996275,"responseStart":977.0999999996275,"secureConnectionStart":502.69999999925494},{"duration":474.7999999988824,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":502.80000000074506,"connectEnd":502.80000000074506,"connectStart":502.80000000074506,"domainLookupEnd":502.80000000074506,"domainLookupStart":502.80000000074506,"fetchStart":502.80000000074506,"redirectEnd":0,"redirectStart":0,"requestStart":502.80000000074506,"responseEnd":977.5999999996275,"responseStart":977.5999999996275,"secureConnectionStart":502.80000000074506},{"duration":474.80000000074506,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":503.09999999962747,"connectEnd":503.09999999962747,"connectStart":503.09999999962747,"domainLookupEnd":503.09999999962747,"domainLookupStart":503.09999999962747,"fetchStart":503.09999999962747,"redirectEnd":0,"redirectStart":0,"requestStart":503.09999999962747,"responseEnd":977.9000000003725,"responseStart":977.9000000003725,"secureConnectionStart":503.09999999962747},{"duration":530.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bv2/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":503.30000000074506,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":503.30000000074506,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1033.800000000745,"responseStart":0,"secureConnectionStart":0},{"duration":475.09999999962747,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":503.40000000037253,"connectEnd":503.40000000037253,"connectStart":503.40000000037253,"domainLookupEnd":503.40000000037253,"domainLookupStart":503.40000000037253,"fetchStart":503.40000000037253,"redirectEnd":0,"redirectStart":0,"requestStart":503.40000000037253,"responseEnd":978.5,"responseStart":978.4000000003725,"secureConnectionStart":503.40000000037253},{"duration":530.4000000003725,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bv2/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":503.59999999962747,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":503.59999999962747,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1034,"responseStart":0,"secureConnectionStart":0},{"duration":475.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/719848dd97ebe0663199f49a3936487a-CDN/lu2bv2/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":503.80000000074506,"connectEnd":503.80000000074506,"connectStart":503.80000000074506,"domainLookupEnd":503.80000000074506,"domainLookupStart":503.80000000074506,"fetchStart":503.80000000074506,"redirectEnd":0,"redirectStart":0,"requestStart":503.80000000074506,"responseEnd":979.3000000007451,"responseStart":979.3000000007451,"secureConnectionStart":503.80000000074506},{"duration":1306.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":509.80000000074506,"connectEnd":509.80000000074506,"connectStart":509.80000000074506,"domainLookupEnd":509.80000000074506,"domainLookupStart":509.80000000074506,"fetchStart":509.80000000074506,"redirectEnd":0,"redirectStart":0,"requestStart":509.80000000074506,"responseEnd":1816.300000000745,"responseStart":1816.300000000745,"secureConnectionStart":509.80000000074506},{"duration":1392.9000000003725,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":510.59999999962747,"connectEnd":510.59999999962747,"connectStart":510.59999999962747,"domainLookupEnd":510.59999999962747,"domainLookupStart":510.59999999962747,"fetchStart":510.59999999962747,"redirectEnd":0,"redirectStart":0,"requestStart":510.59999999962747,"responseEnd":1903.5,"responseStart":1903.5,"secureConnectionStart":510.59999999962747},{"duration":774.5999999996275,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1047.5,"connectEnd":1047.5,"connectStart":1047.5,"domainLookupEnd":1047.5,"domainLookupStart":1047.5,"fetchStart":1047.5,"redirectEnd":0,"redirectStart":0,"requestStart":1047.5,"responseEnd":1822.0999999996275,"responseStart":1822.0999999996275,"secureConnectionStart":1047.5}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":286,"responseStart":496,"responseEnd":512,"domLoading":500,"domInteractive":1929,"domContentLoadedEventStart":1929,"domContentLoadedEventEnd":1993,"domComplete":3720,"loadEventStart":3720,"loadEventEnd":3723,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1905.5999999996275},{"name":"bigPipe.sidebar-id.end","time":1906.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1906.699999999255},{"name":"bigPipe.activity-panel-pipe-id.end","time":1909.300000000745},{"name":"activityTabFullyLoaded","time":1999.5}],"measures":[],"correlationId":"dd702f8c6d2336","effectiveType":"4g","downlink":9.9,"rtt":0,"serverDuration":110,"dbReadsTimeInMs":19,"dbConnsTimeInMs":27,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}