MySQL 8中创建带外键约束表时的元数据锁问题

问题复现 #

• 创建父表：

CREATE TABLE parent_meta_lock_test (SEID BIGINT(19) NOT NULL,
ID BIGINT(19) NOT NULL,
VALUE1 VARCHAR(255),
PRIMARY KEY (ID)
) ENGINE=INNODB;

• 客户端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)

• 客户端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;

此时客户端2会等待parent_meta_lock_test表的元数据锁，查询performance_schema.metadata_locks可见该锁处于PENDING状态，后续所有查询该父表的请求也会陷入元数据锁等待。该问题在MySQL 5.7.30中未出现，迁移至MySQL 8后触发。

原因分析 #

MySQL 8.0对元数据锁（Metadata Locking）机制做了关键调整：创建带外键约束的子表时，必须获取父表的排他元数据锁（X锁），防止子表创建过程中父表结构被修改。而MySQL 5.7中，创建外键仅需父表的共享元数据锁（S锁），与客户端1事务持有的S锁兼容，不会触发阻塞。

客户端1的事务执行SELECT后，会持有父表的共享元数据锁，此时客户端2请求的排他锁与该锁不兼容，进入等待队列；后续查询父表的请求会排队等待前面的排他锁释放，进而引发连锁阻塞。

解决方案 #

1. 及时结束事务：让客户端1执行COMMIT或ROLLBACK，释放持有的共享元数据锁，客户端2的建表操作即可继续执行。
2. 错开事务与DDL时机：避免在长事务持有父表元数据锁期间执行创建子表的DDL操作，尽量选择业务低峰期或无活跃事务的时段进行。
3. 优化事务设计：缩短事务执行时间，避免不必要的长事务持有元数据锁，减少锁冲突概率。

内容的提问来源于stack exchange，提问作者CrazyMinion