MySQL 5.7添加列注释时排他锁与共享读锁引发死锁/冻结

算力阿峰

2026-7-23

问题描述

执行以下ALTER语句为InnoDB表table1的id列添加注释（未修改列类型及其他参数）：

ALTER TABLE `table1` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'test1';

在中等流量生产服务器上执行时，出现类似死锁的阻塞场景：申请SHARED_READ锁的请求处于PENDING状态，EXCLUSIVE锁也处于PENDING状态，系统冻结无法处理该表的查询。

查阅文档得知MySQL对InnoDB表执行DDL时应该允许DML事务，疑惑是否遗漏了什么，同时想确认这是否是MySQL固有特性导致中等流量系统无法执行此类表结构变更。

用于查看锁情况的查询语句：

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID, PROCESSLIST_ID, PROCESSLIST_HOST, PROCESSLIST_STATE, PROCESSLIST_INFO, PROCESSLIST_TIME
FROM performance_schema.metadata_locks 
LEFT JOIN performance_schema.threads ON threads.THREAD_ID = metadata_locks.OWNER_THREAD_ID
order by owner_thread_id DESC;

锁查询结果：

OBJECT_TYP OBJECT_SCHEMA     OBJECT_NAME   LOCK_TYPE           LOCK_DURATION LOCK_STATUS OWNER_THREAD_ID  PROCESSLIST_ID PROCESSLIST_HOST PROCESSLIST_STATE          PROCESSLIST_INFO                PROCESSLIST_TIME
TABLE      main_db           table2        SHARED_READ         TRANSACTION   GRANTED     80801101         80801075       IP2              Waiting for table metadata SELECT FROM table1              16
TABLE      main_db           table1        SHARED_READ         TRANSACTION   PENDING     80801101         80801075       IP2              Waiting for table metadata SELECT FROM table1              16
TABLE      main_db           table1        SHARED_READ         TRANSACTION   PENDING     80799419         80799393       IP1              Waiting for table metadata SELECT FROM table1 and table2   11
GLOBAL     NULL              NULL          INTENTION_EXCLUSIVE STATEMENT     GRANTED     80777210         80777184       IP1              Waiting for table metadata ALTER TABLE table1 add comment  17
SCHEMA     main_db           NULL          INTENTION_EXCLUSIVE TRANSACTION   GRANTED     80777210         80777184       IP1              Waiting for table metadata ALTER TABLE table1 add comment  17
TABLE      main_db           table1        SHARED_UPGRADABLE   TRANSACTION   GRANTED     80777210         80777184       IP1              Waiting for table metadata ALTER TABLE table1 add comment  17
TABLE      main_db           table1        EXCLUSIVE           TRANSACTION   PENDING     80777210         80777184       IP1              Waiting for table metadata ALTER TABLE table1 add comment  17

等待30秒后仍无法获取锁，查询持续阻塞。

问题分析与解决方案

核心原因

这不是死锁，而是元数据锁（MDL）的排队阻塞，属于MySQL处理DDL的固有机制，但完全可以通过优化避免。

从锁信息能看出：

执行ALTER的线程已持有table1的SHARED_UPGRADABLE元数据锁，正在等待EXCLUSIVE锁；
两个SELECT线程持有其他表的SHARED_READ锁，同时等待table1的SHARED_READ锁，但因为ALTER线程先持有了可升级的共享锁，导致后续读锁请求排队等待。

MySQL的MDL机制要求：执行DDL时需先获取SHARED_UPGRADABLE锁，之后升级为EXCLUSIVE锁，升级过程要等所有已存在的SHARED_READ/SHARED_WRITE锁释放，同时新的DML/查询请求会被阻塞，直到DDL完成。

你提到的"InnoDB表执行DDL允许DML"是Online DDL特性，但并非所有DDL都能完全在线。你用的CHANGE语句会触发全量表结构重建（哪怕列类型没改），拉长了DDL执行时间，导致MDL锁等待窗口变大，中等流量下就容易出现大面积阻塞。

优化方案

使用高效DDL语句
仅添加列注释无需用CHANGE，改用更轻量的语句：
```
-- 仅修改列注释，不会重建表
ALTER TABLE `table1` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'test1';
```
MySQL 8.0.23+还支持更简洁的语法：
```
ALTER TABLE `table1` COMMENT COLUMN `id` = 'test1';
```
这类操作属于"仅修改元数据"的Online DDL，几乎不会阻塞DML，也不需要长时间持有EXCLUSIVE锁。
选低峰期执行DDL
尽量在业务流量最低的时段操作，减少同时存在的长事务或慢查询，降低MDL锁等待概率。
清理阻塞的长事务
查询并终止持有table1元数据锁的长会话：
```
SELECT trx_id, trx_started, trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING';
```
拿到trx_mysql_thread_id后，用KILL [线程ID]终止会话，释放MDL锁。
调整MDL锁等待超时
MySQL 5.7+可设置lock_wait_timeout缩短DDL锁等待时间，避免长时间阻塞：
```
SET GLOBAL lock_wait_timeout = 60; -- 设置为60秒
```
这样DDL若60秒内无法获取锁就会失败，避免拖垮整个系统。

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

MySQL 5.7添加列注释时排他锁与共享读锁引发死锁/冻结

核心原因 #

优化方案 #

核心原因

优化方案