MySQL / 系统课时

控制 LOCK 并阻止静默降级

控制 LOCK 并阻止静默降级

线上 DDL 最危险的情况之一,是你以为语句会保持并发写入,MySQL 却因为默认策略改用更重的算法或锁级别。解决办法是同时显式声明 ALGORITHM 和 LOCK,把“必须满足的上线条件”写进 SQL,不满足就立即失败。

学习目标

  • 区分 LOCK=NONE、SHARED、EXCLUSIVE 对并发的约束
  • 理解省略 ALGORITHM 或使用 DEFAULT 时可能发生的算法回退
  • 用显式算法和锁级别让不符合上线要求的 DDL 直接报错

LOCK 表达的是最低并发要求

LOCK 不是让你选择一种固定内部锁,而是声明执行 DDL 时业务至少需要保留多少并发能力。

写法 并发读取 并发写入 不支持时
LOCK=NONE 允许 允许 失败
LOCK=SHARED 允许 阻止 失败
LOCK=EXCLUSIVE 阻止 阻止 强制独占访问
LOCK=DEFAULT 由 MySQL 选择 由 MySQL 选择 可能采用更严格级别

对于要求不停写的业务,真正的保护条件通常是 LOCK=NONE。如果目标操作做不到并发读写,语句应该失败,而不是“尽量执行”。

ALGORITHM 决定能否退回更重路径

省略 ALGORITHM 时,MySQL 会优先使用更轻的受支持算法:能用 INSTANT 就用 INSTANT,否则尝试 INPLACE,再不支持时可能退到 COPY。显式写出 ALGORITHM=INPLACE 或 ALGORITHM=INSTANT 后,如果该变更无法满足指定算法,服务器会报错。

把算法和锁要求组合起来,就能形成一条发布护栏:例如发布策略要求“只能 INPLACE 且必须保持并发写”,那么 SQL 中就应同时写明这两个条件。它表达的不是“尽量在线执行”,而是“任一条件不成立就失败”,把风险交回发布流程处理。

ALGORITHM 与 LOCK 共同决定 DDL 是否允许执行

用失败验证护栏真的生效

下面使用单字节字符集,把 VARCHAR(255) 扩到 VARCHAR(256)。这个变化跨过长度编码边界,不能用 INPLACE 完成。显式要求 ALGORITHM=INPLACE, LOCK=NONE 后,语句应失败。

sql
DROP TABLE IF EXISTS ddl_lock_guard;

CREATE TABLE ddl_lock_guard (
    id BIGINT PRIMARY KEY,
    note VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE ddl_lock_guard
    MODIFY COLUMN note VARCHAR(256) NOT NULL,
    ALGORITHM=INPLACE,
    LOCK=NONE;

预期结果

text
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

这里的失败正是预期行为:生产发布要求“不允许 COPY、必须保持写入”,服务器发现无法满足后立即停止,没有把语句悄悄改成表复制。

如果业务已经评估过写阻塞窗口,并明确接受 COPY,可以重新提交更符合真实约束的语句:

sql
ALTER TABLE ddl_lock_guard
    MODIFY COLUMN note VARCHAR(256) NOT NULL,
    ALGORITHM=COPY,
    LOCK=SHARED;

SELECT COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'ddl_lock_guard'
  AND COLUMN_NAME = 'note';

预期结果

text
+--------------+
| COLUMN_TYPE  |
+--------------+
| varchar(256) |
+--------------+

这次成功并不表示风险消失,而是说明发布者已经主动选择了“允许读、阻止写”的 COPY 路径。

把失败纳入发布流程

生产 DDL 可以先根据业务 SLO 写出约束,再决定是否执行:必须不停写时使用支持该变更的算法并要求 LOCK=NONE;允许短期停写但必须可读时才考虑 LOCK=SHARED;只有维护窗口才接受 LOCK=EXCLUSIVE。

还要注意,LOCK=NONE 只能限制 DDL 执行期间所允许的并发级别,不能消除元数据锁等待。长事务仍可能让 DDL 在开始或结束阶段等待。因此,发布前还要单独检查长事务和 metadata lock。

真正可靠的治理原则是:默认不替你承担业务风险。把 ALGORITHM 和 LOCK 当成上线契约,宁可因为约束不满足而让发布失败,也不要让服务器在未知情况下选择更重的执行路径。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

控制 LOCK 并阻止静默降级

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通