While creating table FK constraint, facing meta data locking issue.
First create a table.
CREATE TABLE parent_meta_lock_test (SEID BIGINT(19) NOT NULL,
ID BIGINT(19) NOT NULL,
VALUE1 VARCHAR(255),
PRIMARY KEY (ID)
) ENGINE=INNODB;
Client 1;
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * from parent_meta_lock_test limit 1;
Empty set (0.02 sec)
Client 2:
mysql> CREATE TABLE child_meta_lock_test ( ID BIGINT(19) NOT NULL, PARENT_ID BIGINT(19) NOT NULL, PRIMARY KEY (id), CONSTRAINT FOREIGN KEY (PARENT_ID) REFERENCES parent_meta_lock_test (ID) ON DELETE CASCADE) ENGINE=INNODB;
Here Client 2 is waiting for metadata lock for parent_meta_lock_test
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+-----------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+-----------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | test | parent_meta_lock_test | NULL | 105553151881808 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6140 | 48 | 11 |
| SCHEMA | test | NULL | NULL | 105553151881488 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:108 | 48 | 11 |
| GLOBAL | NULL | NULL | NULL | 105553151880848 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5475 | 49 | 7 |
| BACKUP LOCK | NULL | NULL | NULL | 105553151880768 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5482 | 49 | 7 |
| SCHEMA | test | NULL | NULL | 105553151880208 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5462 | 49 | 7 |
| TABLE | test | child_meta_lock_test | NULL | 105553151880448 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:6140 | 49 | 7 |
| SCHEMA | test | NULL | NULL | 105553151880528 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:9920 | 49 | 7 |
| TABLE | test | parent_meta_lock_test | NULL | 105553151881888 | EXCLUSIVE | STATEMENT | PENDING | sql_table.cc:9912 | 49 | 7 |
| TABLE | performance_schema | metadata_locks | NULL | 105553151818512 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6140 | 50 | 10 |
+-------------+--------------------+-----------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
9 rows in set (0.01 sec)
After this all upcoming clients which query parent_meta_lock_test are also waiting for metadata lock.
Same issue was not occurring Mysql 5.7.30. when i migrate to Mysql 8 faced this issue.