MySQL 8.0.30加表写锁后SELECT *阻塞，指定列查询正常的原因排查

问题场景 #

在MySQL 8.0.30版本中，对InnoDB表执行LOCK TABLE ... WRITE后，会话会持有SHARED_NO_READ_WRITE类型的MDL锁。此时：

• 执行指定列名的SELECT amount FROM account WHERE id = 1可正常完成
• 执行SELECT * FROM account WHERE id = 1则会陷入Waiting for table metadata lock阻塞状态

建表SQL #

CREATE TABLE `account` (
  `amount` decimal(10,0) DEFAULT '0',
  `id` int NOT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

复现SQL #

set autocommit=0;
show VARIABLES like 'autocommit';

begin;
lock table account write;
-- 正常执行
select amount from account where id = 1;
-- 阻塞
select * from account where id = 1;

unlock TABLES;
commit;

阻塞状态验证 #

执行show PROCESSLIST;结果：

Waiting for table metadata lock. SELECT * FROM order.account LIMIT 0;

执行SELECT OBJECT_NAME,LOCK_TYPE FROM performance_schema.metadata_locks;结果：

account SHARED_NO_READ_WRITE
order/account INTENTION_EXCLUSIVE
session_status SHARED_READ
metadata_locks SHARED_READ

原因解析 #

1. 表锁与MDL锁的交互：执行LOCK TABLE account WRITE时，会话会同时获取两种锁：
   • InnoDB的表级写锁，用于控制表的读写权限
   • SHARED_NO_READ_WRITE类型的MDL锁，用于保护表元数据的一致性
2. SHARED_NO_READ_WRITE锁的限制：该锁的设计逻辑是，持有它的会话仅允许执行明确指定列的查询或DML操作——这类操作不需要动态读取表的完整元数据（比如列定义、新增列等）。
3. *SELECT 的触发逻辑：SELECT *需要动态获取表的所有列元数据来解析查询，此时MySQL会尝试获取SHARED_READ类型的MDL锁以读取元数据。但SHARED_NO_READ_WRITE与SHARED_READ锁在同一会话中存在兼容性冲突，导致会话自身陷入元数据锁等待，最终表现为Waiting for table metadata lock。

解决建议 #

• **避免使用SELECT ***：在持有表写锁的会话中，查询时明确指定需要的列名，绕过元数据动态读取逻辑。
• 替换表级锁为行级锁：如果业务允许，使用InnoDB原生的行级锁（如SELECT ... FOR UPDATE）替代表级写锁，既可以保证数据一致性，又不会触发MDL锁的冲突问题。
• 规范锁使用流程：若必须使用表级写锁，提前确认表元数据不会发生变更，且后续所有查询操作均使用明确列名。

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