云数据库 MySQL 上给订单表加索引,语句写了 ALGORITHM=INPLACE, LOCK=NONE,预期读写都能继续。几秒后接口超时,SHOW FULL PROCESSLIST 里这条 ALTER 和大量 SELECT 都是 Waiting for table metadata lock。表没有进入行锁等待。堵住查询的是元数据锁排队:一把还没授予的排他锁,优先级高于后来的读请求。
LOCK=NONE 限制的是执行阶段的并发,不是全程不加元数据锁。MySQL 8.4 手册把在线 DDL 分成三段。初始化先拿可升级的共享元数据锁,保护当前表定义。执行阶段是否升成排他,取决于引擎、操作和 ALGORITHM、LOCK;需要的话只在准备语句时短暂持有。提交表定义时一定会升成排他,用来换出旧定义、写入新定义,授予之后持有时间很短。
前面若有未结束事务占着这张表的元数据锁,排他锁拿不到,DDL 停在等锁,拷数据还没开始。ALGORITHM=INSTANT 也不是零锁:只改数据字典,表数据不动,执行阶段仍可能短暂拿排他锁。LOCK 不能强行放宽。指定的级别比该操作允许的更松,语句直接报错,不会悄悄改成表拷贝。上线前在结构相同的小表上跑一遍,确认是秒回还是报不支持。
未结束事务里用过的表,其他会话不能对它做 DDL,元数据锁保持到事务结束。自动提交打开时,锁在语句结束就放。关掉自动提交或 START TRANSACTION 之后,一次 SELECT 的共享锁也要留到 COMMIT 或 ROLLBACK。非事务表同样如此。连接断开后,服务器回滚未结束的事务,这把锁才放。
手册里的顺序是:会话 1 开事务并查询;会话 2 的在线 DDL 等排他锁;会话 3 的查询排在这把尚未授予的排他锁后面,不会插队。两个等待者都是 Waiting for table metadata lock,此时 ALTER 往往还没改到行。
START TRANSACTION;
SELECT id FROM shop.orders WHERE id = 1;
ALTER TABLE shop.orders
ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
SELECT id FROM shop.orders WHERE id = 1;上面三条要分属三个会话。写锁请求优先于读锁请求。max_write_lock_count 默认很大,正常不会让读请求插到挂起的写请求前面。队头一旦是 DDL 的排他请求,新的 SELECT、UPDATE 都会排上去,超时重试再占满连接池。有外键时锁会扩到相关表,只查正在 ALTER 的表会漏掉占锁者。
连接池里常见的占锁者是 Sleep:关掉自动提交,查询后没提交就把连接还回池,Info 为空,共享锁还在。不要和 Waiting for table level lock 混淆,那是表级锁 THR_LOCK,不是元数据锁。
元数据锁等待由 lock_wait_timeout 计时,默认 31536000 秒,范围 1 到 31536000,全局和会话都可改,也支持 SET_VAR。它覆盖使用元数据锁的 DML、DDL、LOCK TABLES 和 FLUSH TABLES WITH READ LOCK。超时按每次加锁单独计算,一条语句要多把锁时总时间可以更长。超时后报 ER_LOCK_WAIT_TIMEOUT。
行锁由 innodb_lock_wait_timeout 计时,默认 50 秒,作用于 InnoDB 行锁,不作用于 MySQL 层的表锁。超时后当前语句回滚,事务默认还在,除非打开 innodb_rollback_on_timeout。两边都可能落到错误号 1205、文本 Lock wait timeout exceeded; try restarting transaction。区分看超时前的状态:元数据锁是 Waiting for table metadata lock;行锁看 innodb_trx.TRX_STATE = 'LOCK WAIT',锁细节在 performance_schema.data_locks。把行锁超时改成 5 秒,DDL 仍按 lock_wait_timeout 等。
不要把 lock_wait_timeout 全局改得很小,DML 拿元数据锁同样受它约束。只在 DDL 会话里收短,而且 SET SESSION 必须和 ALTER 在同一条连接上,不要被连接池拆开。拿不到就失败,挂起的排他请求撤销,后面的查询才能继续。
SET SESSION lock_wait_timeout = 10;
ALTER TABLE shop.orders
ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;8.4 手册写明探针 wait/lock/metadata/sql/mdl 默认开启。其他版本先查 performance_schema.setup_instruments 里该行的 ENABLED。表是空的,可能是探针没开,不是没有锁。
PENDING 是还没拿到的请求。其他会话上 GRANTED 且 LOCK_DURATION = 'TRANSACTION' 的,就是占锁者。KILL 用 PROCESSLIST_ID,不要用 THREAD_ID。
SELECT ml.LOCK_TYPE, ml.LOCK_STATUS, ml.LOCK_DURATION,
t.PROCESSLIST_ID, t.PROCESSLIST_COMMAND,
t.PROCESSLIST_TIME, t.PROCESSLIST_STATE, t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
AND ml.OBJECT_SCHEMA = 'shop'
AND ml.OBJECT_NAME = 'orders';sys.schema_table_lock_waits 把等待方、阻塞方和两条 KILL 拼在一起,列包括 waiting_pid、blocking_pid、sql_kill_blocking_connection。阻塞方已是 Sleep 时没有语句可杀。KILL QUERY 只终止当前语句,连接还在,事务不结束,元数据锁不放。能联系到业务就让它提交或回滚;连接没人负责,用 KILL CONNECTION。断开后未结束事务会回滚,事务级元数据锁才释放。KILL 只是置标志,返回后再查一次,确认没有残留的 PENDING。
回滚大事务会带来额外写入。先看 information_schema.innodb_trx 的 TRX_STARTED 和 TRX_WEIGHT(反映改动和加锁规模,不是精确行数)。只读且不加锁的事务不生成 TRX_ID,这张表不能代替 metadata_locks。
急救先停 DDL。查询是被尚未授予的排他请求堵住的。对这条 ALTER 执行 KILL QUERY,或等会话级超时失败,请求撤销后队列才会散。长事务留到后面再收。
《云数据库 MySQL 使用规范》建议避开业务高峰做在线 DDL,并提到可以用 pt-online-schema-change。它建影子表、分块拷贝,用触发器同步增量,最后用一次原子 RENAME TABLE 交换名字。拷贝阶段不用一直拿着原表的排他锁。
RENAME 仍要排他元数据锁。未结束事务还在时,切换变成新的队头,后面的查询继续排。工具默认设置 lock_wait_timeout=60、innodb_lock_wait_timeout=1,让自己更可能先失败,而不是把别人堵住一年。--set-vars 只覆盖写到的变量。锁等待超时后会重试;交换表名通常只有一条 RENAME。长事务还在时,反复重试只是把一次长阻塞换成多次短阻塞。创建、删除触发器受 lock_wait_timeout 影响,拷贝行受 innodb_lock_wait_timeout 影响。
pt-online-schema-change --alter "ADD INDEX idx_created (created_at)" --set-vars lock_wait_timeout=10 --host 10.0.0.12 --print --dry-run D=shop,t=orders--dry-run 与 --execute 互斥。前者会创建并改好影子表,但不建触发器、不拷数据、不替换原表。不带 --execute 则只做安全检查就退出。影子表按工具输出的名字删。正式变更再单独加 --execute。
工具会拒绝或丢数据的情况:表上已有触发器;大多数情况下没有主键或唯一索引;有外键引用这张表却没指定 --alter-foreign-keys-method,原子改名对外键不成立;加唯一索引。拷贝用的是 INSERT IGNORE,重复键会被静默丢掉,所以默认拒绝加唯一索引。探针和变量以实例上的 SHOW VARIABLES、setup_instruments 为准,不要拿 8.4 手册的默认值代替。
ALGORITHM 和 LOCK 跑通。更松的 LOCK 报错,就不要在主库上去掉子句硬跑。lock_wait_timeout,且和 ALTER 在同一条连接上。没有把很小的值设到全局。TRANSACTION 级 GRANTED 锁。探针是开的。innodb_trx 里没有又久又重的事务。Waiting for table metadata lock。出现了先停 DDL,不要等默认的一年超时。pt-online-schema-change 时,接受切换瞬间仍要排他锁,并核对触发器、主键、外键、唯一索引。参考文档:
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。