技术分享 | 什么情况下 MySQL 连查询都能被阻塞？

作者：贾特特，MySQL DBA 从业者，公众号『数据库运维札记』作者，目前任职于某游戏公司担任DBA工程师

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 2000 字，预计阅读需要 8 分钟。

MySQL 的锁也是不少，在哪种情况下会连查询都能被阻塞？这是一个有意思的问题。

工作中，很多开发和 DBA 可能接触较多的锁也就行锁了。对于行锁，阻塞写能理解，阻塞读实在是想不到。能阻塞读的那肯定是颗粒度更大的锁了，比如表级别的。

本文操作环境为 MySQL 8.0。

1MySQL 表级锁有两种实现

1. 服务器（SERVER）层：本层的锁定主要是元数据锁（metadata lock，MDL）。
2. 存储引擎（ENGINE）层：本层不同的存储引擎可能会实现不同的锁定策略。例如 MyISAM 引擎实现了表级锁，InnoDB 存储引擎实现了行级锁和表级锁，其中表级锁是通过意向锁体现的。

元数据锁（MDL）是由 SERVER 层管理，用于锁定数据库对象的元数据信息，如：表结构、索引等。元数据锁可以阻止对表结构的改变，以确保数据定义的一致性。

元数据锁的类型

点击放大

每种锁类型后面会详细介绍。简单来说，对于元数据锁而言，当对一个表进行增删改查操作的时候，会加 元数据读锁。当对表数据结构进行变更的时候会加 元数据写锁。它读写互斥，写写互斥，只有读读不冲突。

意向锁是在存储引擎层实现的，用于协调不同事务对表级锁和行级锁的请求。当一个事务在某个层次（表级或行级）上获取锁时，会首先获取对应层次的意向锁，以提示其他事务该事务在该层次上有锁的意向。这样可以在更高层次上减少锁冲突，提高并发性能。

InnoDB 存储引擎的意向锁种类

1. 意向共享锁（Intention Shared Lock，IS）：事务打算给数据行加共享锁（S 锁）。
2. 意向排他锁（Intention Exclusive Lock，IX）：事务打算给数据行加排他锁（X 锁）。

这样看来，表对象不可读写有种情况可能就是元数据锁互斥所导致的。

2Waiting for table metadata lock

本节中未完成的读写事务，在实际中可能是未完成的大事务，也可能是未显式结束的事务。

元数据锁互斥（未完成的读事务）

会话 1 执行：有未完成的读事务，此时获取了元数据共享读锁。

MDL_SHARED_READ： 这个锁允许会话读取表的数据，并允许其他会话获取 SHARED_READ 或 SHARED_WRITE 锁，但不允许获取 SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

会话 2 执行：ALTER 表结构变更语句，此时 ALTER 语句要获取元数据排它锁。

MDL_EXCLUSIVE： 这个锁允许会话读取和修改表的数据和结构，但不允许其他会话获取任何类型的锁。

元数据锁互斥等待，之后其他会话对于所涉及表不可读写。

元数据锁互斥（未完成的写事务）

会话 1 执行：有未完成的写事务，此时获取了元数据写锁。

MDL_SHARED_WRITE： 这个锁允许会话读取和修改表的数据，并允许其他会话获取 SHARED_READ 锁，但不允许获取 SHARED_WRITE、SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

会话 2 执行：ALTER 表结构变更语句，此时 ALTER 语句要获取元数据排它锁。

MDL_EXCLUSIVE：这个锁允许会话读取和修改表的数据和结构，但不允许其他会话获取任何类型的锁。

元数据锁互斥等待，之后其他会话对于所涉及表不可读写。

LOCK TABLES ... READ/WRITE

LOCK TABLES 可以显式获取表锁，需要注意的是会话只能自己获取和释放表锁。UNLOCK TABLES 可以显式释放当前会话的表锁。

LOCK TABLES … READ

会话 1 执行：lock tables db_version read;

MDL_SHARED_READ_ONLY： 这个锁允许会话读取表的数据，并允许其他会话获取 SHARED_READ 锁，但不允许获取 SHARED_WRITE、SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

此时 db_version
加了元数据共享只读锁。

会话 2 执行：ALTER 表结构变更语句，此时 ALTER 语句要获取元数据排它锁，元数据锁互斥等待。

之后所涉及表对象将不可读写。

LOCK TABLES … WRITE

会话 1 执行：lock tables db_version write;

MDL_SHARED_NO_READ_WRITE： 这个锁允许当前会话读取和修改表的数据，但不允许其他会话获取任何类型的锁。

此时 db_version
加上了独占写锁。只能在 会话 1 读写，它会阻止其他会话获取任何类型的锁。

因此其他会话既不能读也不能写，当然查询也会被阻塞了。

需要注意的是，此时 会话 1 对其他表也会不可读写。

3FLUSH TABLES & WAITING FOR TABLE FLUSH

FLUSH TABLES 关闭所有打开的表，强制关闭所有正在使用的表，并刷新准备好的语句缓存。当存在活动的 LOCK TABLES 时，不允许执行 FLUSH TABLES 操作。

FLUSH TABLES

1. 当 ALTER 表结构时，执行 FLUSH TABLES  阻塞，从而导致表对象不可读写。

1. 当  LOCK TABLES  后，执行  FLUSH TABLES  会被阻塞，从而导致表对象不可读写。

• 会话 1 执行：lock tables db_version read;
• 会话 2 执行：flush tables;

此时，会话 2 会被阻塞，其他会话对所涉及表将不可读写。SHOW PROCESSLIST 中会提示 Waiting for table flush
。

需要说明的是，会话 1  执行完 lock tables...read lock
后，其他会话执行 DML 增删改语句，虽然会因获取不到元数据锁而阻塞，但不会阻塞其他会话执行 SELECT 查询。

换言之，执行 lock tables...read
后，当遇到元数据锁排它锁互斥阻塞（ALTER 语句）或者  FLUSH TABLES   发生阻塞后，才会发生所涉及表对象不可读写。

4处理延伸

如何处理并找到源头 SQL 呢？

对于因元数据锁互斥而导致的表不可读写，一般可以通过 sys
库下的内置视图来查看。可能会涉及的表：

sys.schema_table_lock_waits： 可直接通过 sys
下内置视图，看到元数据锁互斥的相关信息。

information_schema.innodb_trx： 找到长时间未提交的事务。

对于因 FLUSH TABLE 等待而导致的表不可读写的场景，通过上述视 图/表 是不一定有数据的。大致会有以下两种情况：

Waiting for table flush： 可以按如下方式寻找源头。这种情况主要出现于因 FLUSH TABLES 而等待后，执行 DML 语句。

SELECT
  b.PROCESSLIST_ID,
  b.THREAD_ID,
  a.OBJECT_NAME,
  a.LOCK_TYPE,
  a.LOCK_STATUS,
  b.PROCESSLIST_STATE 
FROM
  `performance_schema`.metadata_locks a
  LEFT JOIN `performance_schema`.threads b ON a.OWNER_THREAD_ID = b.THREAD_ID 
WHERE
  a.OBJECT_SCHEMA = 'tmp';


也可以通过线程 ID 找到会话最近的 10 条语句进一步判断确认。

select THREAD_ID,event_id,sql_text from 
`performance_schema`.events_statements_history
where THREAD_ID =  14503
order by event_id;


Waiting for table metadata lock： 可以参考元数据锁互斥而导致的表不可读写处理。这种情况主要出现于因 FLUSH TABLES 而等待后，执行 DDL 语句如 ALTER TABLE。

5总结

以下情况会导致表对象不可读写：

1. 因 Waiting for table metadata lock 而导致的表对象不可读写。
2. 因 Waiting for table flush 而导致的表对象不可读写。

本文关键字：#MySQL# #锁# #SQL# #MDL#

故障分析 | 如何解决由触发器导致 MySQL 内存溢出？
技术分享 | MySQL VARCHAR 最佳长度评估实践
故障分析 | TCP 缓存超负荷导致的 MySQL 连接中断
故障分析 | 一则 MySQL 从节点 hung 死问题分析
新特性解读 | MySQL 8.0 支持对单个数据库设置只读
技术分享 | MySQL 授权表运维注意事项
技术分享 | 如何通过 binlog 定位大事务？
新特性解读 | MySQL 8.3 可以打标签的 GTID
故障分析 | MySQL 执行 Online DDL 操作报错空间不足？
技术分享 | 如何缩短 MySQL 物理备份恢复时间？

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle