# Metadata locking - Table creation with FK constraint Mysql 8

**URL:** <https://forums.percona.com/t/metadata-locking-table-creation-with-fk-constraint-mysql-8/27286>\
**Category:** Percona Server for MySQL 8.0\
**Tags:** mysql\
**Created:** [December 20, 2023, 4:24pm UTC](https://forums.percona.com/t/metadata-locking-table-creation-with-fk-constraint-mysql-8/27286 "2023-12-20T16:24:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tamil\_maran](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tamil_maran/32/14292_2.png) [@tamil\_maran](https://forums.percona.com/u/tamil_maran)\
**Post date:** [December 20, 2023, 4:24pm UTC](https://forums.percona.com/t/metadata-locking-table-creation-with-fk-constraint-mysql-8/27286/1 "2023-12-20T16:24:50Z")

</div>

While creating table FK constraint, facing meta data locking issue.  
First create a table.

```auto
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;

```auto
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:

```auto
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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Corrado\_Pandiani](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/corrado_pandiani/32/1459_2.png) [@Corrado\_Pandiani](https://forums.percona.com/u/Corrado_Pandiani)\
**Post date:** [December 22, 2023, 3:20pm UTC](https://forums.percona.com/t/metadata-locking-table-creation-with-fk-constraint-mysql-8/27286/2 "2023-12-22T15:20:31Z")

</div>

Hi Tamil,

I think this is related to the following from teh 8.0 documentation

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

[https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html)