MySQL｜select+alter 造成阻塞
最新回答
宅m
2026-07-20 14:28:03
MySQL中SELECT操作阻塞ALTER操作的根本原因是元数据锁（MDL）的持有与等待机制。当SELECT语句处于未提交的事务中时，会持续持有MDL读锁，导致后续的ALTER操作因无法获取MDL写锁而被阻塞，进而引发连锁反应阻塞其他DML操作。以下是详细解析：
1. 阻塞原因：MDL锁机制MDL锁的作用MySQL通过MDL锁保护表结构不被并发修改。执行任何DDL（如ALTER）前需获取MDL排他锁（X锁），而SELECT等DML操作会持有MDL共享锁（S锁）。
阻塞场景
事务一：SELECT * FROM rate_table在未提交的事务中，持续持有MDL S锁。
事务二：ALTER TABLE rate_table ADD COLUMN x INT尝试获取MDL X锁，但因S锁未释放而被阻塞（状态为Waiting for table metadata lock）。
后续DML：其他SELECT或DML操作因等待ALTER的X锁释放，形成级联阻塞。
- Session 1（开启事务并执行SELECT）：SET autocommit=0;BEGIN;SELECT * FROM test.rate_table; -- 事务未提交，MDL S锁持续持有
- Session 2（执行ALTER被阻塞）：ALTER TABLE test.rate_table ADD COLUMN n INT; -- 等待MDL X锁
- Session 3（后续DML被阻塞）：SELECT * FROM test.rate_table; -- 等待ALTER释放锁
- 查看阻塞链通过SHOW PROCESSLIST发现大量Waiting for table metadata lock的会话，但无法直接定位阻塞源。
- 精准定位锁持有者使用以下SQL查询持有MDL锁的事务和会话ID：SELECT d.trx_started, a.thread_id, b.processlist_id, a.SQL_text FROM performance_schema.events_statements_current a JOIN performance_schema.threads b ON a.thread_id=b.thread_id JOIN information_schema.processlist c ON b.processlist_id=c.id JOIN information_schema.innodb_trx d ON c.id=d.trx_mysql_thread_id ORDER BY d.trx_started;
- 及时提交/回滚事务：确保SELECT操作后提交事务，避免长时间持有MDL锁。
- 设置自动提交：在应用驱动（如PyMySQL）中启用autocommit=True，避免默认关闭导致隐式事务。
- 低峰期操作：在业务低峰期执行ALTER，减少对生产环境的影响。
- 使用工具减少阻塞：如pt-online-schema-change或MySQL 8.0的INSTANT ADD COLUMN功能。
- 监控长时间运行的事务，设置阈值告警，及时干预。
MDL锁不仅保护表结构，还覆盖以下对象：
- 数据库模式（Schema）
- 存储过程、函数、触发器
- 表空间、用户锁（通过GET_LOCK()获取）
- 锁定服务（如LOCK TABLES）
SELECT阻塞ALTER的核心在于未提交事务导致的MDL锁未释放。通过规范事务管理、优化DDL执行时机及工具辅助，可有效避免此类问题。同时，利用系统表快速定位阻塞源是解决此类问题的关键。
热门标签
