mysql如何解决Metadata Lock元数据锁冲突_mysql DDL阻塞排查

show processlist 看不到阻塞源头？先查 performance_schema.metadata_locks

mysql 5.7+ 的元数据锁（MDL）不会直接暴露在 SHOW PROCESSLIST 里，光看 State 字段为 Waiting for table metadata lock 只能知道“被卡了”，但不知道谁在 hold 锁。真正要定位，得查 performance_schema.metadata_locks 表——它记录了所有活跃的 MDL 持有和等待关系。

实操建议：

• 确保 performance_schema 已启用（select @@performance_schema 返回 1），且 metadata_locks 表已开启（UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'）
• 执行：

  SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE LOCK_STATUS = 'PENDING';

  找出正在等待的线程；再用这个 OWNER_THREAD_ID 去 performance_schema.threads 查对应 SQL 和连接信息

• 注意：该表只显示当前活跃锁状态，不保留历史；如果锁释放得快，可能查不到，需配合 sys.schema_table_lock_waits 视图（MySQL 5.7.19+ 自带）快速定位

ALTER TABLE 被卡住时，为什么 kill 不掉长事务反而更糟？

DDL（如 ALTER TABLE）需要获取 MDL_EXCLUSIVE 锁，而任何未提交的事务（哪怕只是 SELECT）只要访问过目标表，就会持有 MDL_SHARED_READ 或 MDL_SHARED_WRITE 锁，形成阻塞链。此时若盲目 KILL 那个“看起来不重要”的连接，可能触发回滚——而大事务回滚本身会持续持有 MDL 锁，甚至拖更久。

实操建议：

• 先确认阻塞源是否是长事务：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW()) - TIME_TO_SEC(TRX_STARTED) > 60;
• 若确实是长事务且可中断，优先用 KILL QUERY thread_id（只终止语句，不杀连接），避免回滚开销；只有确认事务无副作用时才用 KILL CONNECTION
• 对业务关键表，DDL 前应主动检查 information_schema.PROCESSLIST 中是否有长时间运行的 SELECT、UPDATE 或空闲连接（Command = 'Sleep' 但 Time > 300）

MySQL 8.0 的 ALGORITHM=INSTANT 真的不锁表吗？

INSTANT 算法确实跳过 MDL 排他锁，但它只支持极有限的操作：仅限添加列（ADD column）、重命名列（RENAME COLUMN）、修改列默认值（ALTER COLUMN ... SET default）。一旦涉及数据变更（比如加索引、改类型、删列），就会退化为 copy 或 INPLACE，照样需要 MDL_EXCLUSIVE 锁。

实操建议：

• 执行前务必验证：ALTER TABLE t1 ADD COLUMN c1 int DEFAULT 0, ALGORITHM=INSTANT; 若报错 ALGORITHM=INSTANT is not supported...，说明操作不兼容，别硬加参数
• 8.0.12+ 支持 SHOW CREATE TABLE 输出中看到 ALGORITHM=INSTANT 是否生效；也可查 information_schema.INNODB_TABLES 的 CREATE_TIME 和 UPDATE_TIME 是否变化来间接判断
• INSTANT 不解决 DML 阻塞问题——即使 DDL 不锁表，高并发写入仍可能因行锁或间隙锁导致 DML 延迟，这不是 MDL 的锅

线上不敢停写，又想安全做 DDL？用 pt-online-schema-change 的前提条件

pt-online-schema-change（pt-osc）本质是用触发器双写+重命名绕过 MDL，但它不是银弹。触发器本身会引入额外开销，且对主从延迟、binlog 格式、大字段都有隐性要求。

实操建议：

• 必须开启 ROW 格式 binlog（binlog_format = ROW），否则触发器无法捕获变更；MIXED 或 STATEMENT 下 pt-osc 会拒绝执行
• 原表不能有外键、全文索引、空间索引；也不能是临时表或分区表（8.0.23+ 分区表支持有限）
• 执行前用 --dry-run 和 --execute 组合预演，重点观察 Threads_running 是否飙升、从库延迟是否扩大；若主从延迟已超 30 秒，暂停执行

MDL 冲突的本质是并发控制粒度问题，不是配置调优能根治的。真正难处理的，永远是那些没显式事务包裹、却在应用层悄悄 hold 住连接几十秒的“幽灵查询”。