事务与并发（ACID / 隔离级别 / 锁 / MVCC） · 锁机制

什么是元数据锁（MDL）？为什么 DDL 和 DML 之间会互相阻塞？

What is a Metadata Lock (MDL)? Why do DDL and DML statements block each other

答案语言

考察要点

这道题面试官想看你是否深入理解 MySQL 的并发控制机制，尤其是在数据定义语言（DDL）和数据操作语言（DML）交互时，为保证数据一致性而引入的保护措施。核心考察点包括：

元数据锁（MDL）的本质：是否理解它是一种服务层（Server-Level）的锁，用于保护数据库对象的元数据（如表结构）。
锁的类型与兼容性：是否清楚 MDL 分为共享锁（SHARED）和排他锁（EXCLUSIVE），并理解它们之间的互斥关系。
阻塞链路的形成：能否清晰描述一个“长事务”如何引发 DDL 等待，进而导致后续所有对该表的 DML 请求被阻塞的经典场景。

核心概念

1. 什么是元数据锁（MDL）

元数据锁（Metadata Lock, MDL）是 MySQL 5.5 版本引入的一种锁机制，用于在并发环境下保护数据库对象的元数据信息，防止在 DML 操作执行期间，表的结构发生变更，从而保证数据的一致性。

保护对象：表、视图、存储过程、函数、触发器等数据库对象的元数据（结构定义）。
作用层级：MDL 是在 MySQL Server 层实现的，独立于存储引擎（如 InnoDB, MyISAM）。
目的：解决 DDL 与 DML/DDL 之间的并发冲突问题，确保事务的隔离性。例如，防止一个事务正在查询某列时，另一个会话通过 ALTER TABLE 把该列删掉。

2. MDL 的锁类型与兼容性

MDL 主要分为两大类：共享元数据锁（Shared Metadata Lock）和排他元数据锁（Exclusive Metadata Lock）。

共享锁（SHARED）：
- SHARED_READ / SHARED_WRITE：由 DML 操作（SELECT, INSERT, UPDATE, DELETE）获取。
- 兼容性：共享锁之间是兼容的。这意味着多个 DML 操作可以并发地在同一张表上执行。
排他锁（EXCLUSIVE）：
- EXCLUSIVE：由 DDL 操作（ALTER TABLE, DROP TABLE, TRUNCATE TABLE 等）获取。
- 兼容性：排他锁与任何锁（包括共享锁和排他锁）都是互斥的。这意味着执行 DDL 操作时，必须等待所有在该表上的活动事务（DML）结束。反之，一旦 DDL 开始执行或等待执行，它会阻塞所有新的 DML 请求。

兼容性矩阵

锁类型	SHARED_READ/WRITE	EXCLUSIVE
SHARED_READ/WRITE	✅ 兼容	❌ 互斥
EXCLUSIVE	❌ 互斥	❌ 互斥

3. DDL 与 DML 互相阻塞的经典场景

MDL 锁的申请遵循一个队列机制。当一个会话需要获取 MDL 锁时，如果该锁与其他会话持有的锁冲突，它就会进入队列等待。这个机制是导致“DDL 阻塞 DML”问题的关键。

场景复现： 假设有一张表 t，存在三个数据库会话 A、B、C。

会话 A (长事务)：启动一个事务，并执行一个查询，但不提交。
sql
```
 1-- Session A
 2BEGIN;
 3SELECT * FROM t WHERE id = 1;
 4-- (不 COMMIT，事务保持活跃)
```
此时，会话 A 持有表 t 的 SHARED_READ 类型的 MDL 锁。
会话 B (DDL 操作)：尝试对表 t 进行结构变更。
sql
```
 1-- Session B
 2ALTER TABLE t ADD COLUMN new_col INT;
```
会话 B 尝试获取表 t 的 EXCLUSIVE MDL 锁。由于会话 A 持有 SHARED_READ 锁，两者互斥，因此会话 B 进入等待状态。在 SHOW PROCESSLIST 中，其状态为 Waiting for table metadata lock。
会话 C (新 DML 操作)：此时，一个新的查询请求到达。
sql
```
 1-- Session C
 2SELECT * FROM t WHERE id = 2;
```
会话 C 尝试获取表 t 的 SHARED_READ MDL 锁。虽然 SHARED_READ 锁本身是共享的，但由于 MDL 队列中已经有一个更高优先级的 EXCLUSIVE 锁（来自会话 B）在等待，为了防止写操作（DDL）饿死，后续的读请求（DML）必须排队等待。因此，会话 C 也会被阻塞。

阻塞链路图示：

text

 1Session A (持有 SHARED_READ 锁)
 2    |
 3    └─ blocks ─> Session B (等待 EXCLUSIVE 锁)
 4                      |
 5                      └─ blocks ─> Session C (等待 SHARED_READ 锁)
 6                                    |
 7                                    └─ blocks ─> Session D, E, F ... (所有后续对表 t 的 DML)

结论：一个未提交的长事务（DML）占用了共享 MDL 锁，导致 DDL 操作等待。而这个等待的 DDL 操作，又会阻塞所有后续对该表的 DML 请求。最终结果是，整个表的相关操作都被“卡住”，可能导致线上业务大量请求超时，引发故障。

常见低分回答

混淆 MDL 锁与 InnoDB 行锁/表锁：回答“因为 ALTER TABLE 要锁住整张表，所以会阻塞 DML”，这个说法虽然结果正确，但没有点明 MDL 是 Server 层的机制，也无法解释为什么一个简单的 SELECT 也会被后续阻塞。
只解释了 DDL 被阻塞，没解释 DML 为何也被阻塞：只说明了“长事务 DML 阻塞了 DDL”，但没有解释清楚为什么后续的 DML（如会话 C 的 SELECT）也会被阻塞。关键在于 DDL 请求在队列中阻塞了后续的 DML 请求。
对“长事务”的理解不深：简单地说“有事务在执行”，但没有强调是“未提交”的事务。一个执行完并已提交的事务会立即释放 MDL 锁，不会造成阻塞。问题根源在于事务长时间持有锁而不释放。
认为 TRUNCATE TABLE 是 DML：错误地将 TRUNCATE 归类为 DML。TRUNCATE TABLE 是 DDL，它会请求 EXCLUSIVE MDL 锁，同样会引发上述阻塞问题，并且它不会触发 DELETE 触发器，事务日志也与 DELETE 不同。
回答过于笼统：只说“为了保证数据一致性”，但没有具体说明 MDL 如何通过共享/排他锁机制以及队列策略来实现这一目标。

工程实践与优化

避免长事务：这是最根本的解决方案。应用层代码应确保事务尽快提交或回滚，特别是只读事务。可以开启 autocommit，或者在代码逻辑中确保 commit/rollback 总能被执行。

监控长事务：通过查询 information_schema.innodb_trx 定期巡检，找出运行时间过长的事务，并定位到具体的业务代码或 SQL。

sql

 1SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
 2FROM information_schema.innodb_trx
 3WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60; -- 查找运行超过60秒的事务

为 DDL 设置超时：在执行 DDL 时，可以设置 lock_wait_timeout，避免 DDL 无限期等待，导致大量请求堆积。

sql

 1SET lock_wait_timeout = 10; -- 设置锁等待超时为10秒
 2ALTER TABLE t ADD COLUMN new_col INT;
 3SET lock_wait_timeout = 31536000; -- 恢复默认值

使用在线 DDL 工具：对于大表，应避免直接执行 ALTER TABLE。可以使用 pt-online-schema-change (Percona Toolkit) 或 gh-ost (GitHub) 等工具。它们的原理是创建一个新表，通过触发器将原表的增量数据同步到新表，数据同步完成后，通过原子性的 RENAME TABLE 操作完成切换，整个过程对原表的锁定时间极短。
使用 MySQL 8.0+ 的即时 DDL：MySQL 8.0 对 DDL 做了很多优化，例如添加列（非首列、无默认值）可以实现 ALGORITHM=INSTANT，几乎不产生 MDL 锁等待。

常见追问与延伸题目

追问：如果线上出现了 Waiting for table metadata lock，你如何快速定位并解决问题？
- 回答要点 1 (定位)：
  - 首先执行 SHOW FULL PROCESSLIST;，找到状态为 Waiting for table metadata lock 的线程，记录其 Id 和 Info（即 DDL 语句）。
  - 然后，在 MySQL 5.7+ 中，查询 performance_schema.metadata_locks 表，找到阻塞该 DDL 的线程 ID (OWNER_THREAD_ID)。
  sql
```
 1SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'your_table_name';
```
  - 根据 OWNER_THREAD_ID，回到 SHOW PROCESSLIST 或查询 information_schema.innodb_trx，找到持有 MDL 锁的那个长事务的详细信息（如 trx_started, trx_query, Host）。
- 回答要点 2 (解决)：
  - 联系业务：根据定位到的主机和 SQL 信息，联系相关业务线的开发人员，确认是否可以手动终止该事务。
  - 紧急处理：如果业务允许且情况紧急，执行 KILL <thread_id> 来终止持有锁的那个长事务。一旦该事务回滚并释放锁，DDL 就能获取锁并执行，后续的 DML 也会恢复正常。
  - 事后复盘：分析长事务产生的原因，从代码层面进行优化，避免问题再次发生。
追问：除了长事务，还有哪些场景可能导致 MDL 锁等待？
- 回答要点 1 (显式锁)：LOCK TABLES ... READ/WRITE 语句会持有 MDL 锁，直到 UNLOCK TABLES。如果在持有锁期间会话意外中断，锁不会自动释放，也会导致问题。
- 回答要点 2 (备份)：使用 mysqldump 等工具进行逻辑备份时，如果使用了 --lock-all-tables 或 --master-data 选项，会在备份期间持有全局读锁或特定表的 MDL 锁，此时执行 DDL 会被阻塞。
- 回答要点 3 (查询缓存)：在 MySQL 8.0 之前，如果查询缓存（Query Cache）开启，对一个表执行 DML 后，会导致该表所有相关的查询缓存项失效，这个失效操作也需要获取 MDL 锁，虽然时间很短，但在高并发下也可能成为瓶颈。（注：Query Cache 在 MySQL 8.0 中已移除）。
追问：如果我换成 PostgreSQL，DDL 和 DML 的并发行为有什么不同？
- 回答要点 1 (事务性 DDL)：PostgreSQL 的一个核心优势是其 DDL 是事务性的。你可以将 ALTER TABLE 语句放在一个 BEGIN...COMMIT 块中。如果事务回滚，DDL 操作也会被撤销。MySQL 的 DDL 是隐式提交的，一旦执行就无法回滚。
- 回答要点 2 (锁机制)：PostgreSQL 使用更细粒度的锁（如 ACCESS SHARE, ROW SHARE, ACCESS EXCLUSIVE 等共8种锁级别）。执行 DML（如 SELECT）通常获取 ACCESS SHARE 锁，而 ALTER TABLE 需要 ACCESS EXCLUSIVE 锁。ACCESS EXCLUSIVE 锁与所有其他锁都冲突。其阻塞逻辑与 MySQL 类似：一个长时间运行的事务持有 ACCESS SHARE 锁，会阻塞需要 ACCESS EXCLUSIVE 锁的 DDL，该 DDL 又会阻塞后续所有需要锁的请求。
- 回答要点 3 (非阻塞 DDL)：PostgreSQL 对某些 DDL 操作支持非阻塞模式。例如，创建索引时可以使用 CREATE INDEX CONCURRENTLY，它允许在创建索引期间对表进行读写操作，代价是构建时间更长，且不能在事务块中执行。这与 MySQL 的 Online DDL 工具思想类似，但属于原生支持。