SQL Academy · 课时

在线迁移：ALTER TABLE 为何会加锁

Q: 「在线迁移：ALTER TABLE 为何会加锁」这节课中我会学到什么？

了解哪些 ALTER TABLE 形式会获取 ACCESS EXCLUSIVE 锁并重写表，哪些形式只修改元数据 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解哪些 ALTER TABLE 形式会获取 ACCESS EXCLUSIVE 锁并重写表，哪些形式只修改元数据

在线迁移：ALTER TABLE 为何会加锁 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

生产环境迁移问题

在小型数据库中，ALTER TABLE可以瞬间完成。但在一个正在运行的 500GB 表上，相同的命令可能会锁定写入 20 分钟。了解哪些 ALTER 操作安全、哪些不安全至关重要。

锁级别

PostgreSQL 的锁分为多个级别：

ACCESS SHARE — 查询
ROW EXCLUSIVE — 写入
SHARE / SHARE ROW EXCLUSIVE — 与读取并存的 DDL
EXCLUSIVE — 阻止查询
ACCESS EXCLUSIVE — 阻止 EVERYTHING

ALTER TABLE 会获取什么锁

大多数 ALTER 变体都会获取 ACCESS EXCLUSIVE — 在操作完成前阻止读取和写入。

快速（仅元数据）ALTER

有些 ALTER 只会修改目录，即使面对超大表也能在几毫秒内完成：

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

慢速（重写）ALTER

这些操作会重写整个表：

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

安全地添加 NOT NULL

对于大表：

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

在线添加外键

使用相同的 NOT VALID 技巧：

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

锁等待问题

等待 ACCESS EXCLUSIVE 锁的 ALTER 会排在每个长时间运行的事务之后。较新的事务也会排在该 ALTER 之后 — 形成被阻塞查询的链条。

lock_timeout

不要让迁移永远挂起：

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

重试循环

迁移在发生 lock_timeout 时应该重试：

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

用于安全控制的 statement_timeout

限制迁移中任意单条语句的最长运行时间：

SET statement_timeout = '30s';

有帮助的工具

strong_migrations（Rails）
django-migrate-zero-downtime
pg-osc（Postgres 在线模式变更）
pgRoll

回顾

在线迁移需要了解：

哪些 ALTER 仅修改元数据，哪些会重写表
使用 NOT VALID + VALIDATE 处理约束
设置 lock_timeout 并进行重试
避免阻塞迁移的长时间运行事务

快速检查

您通过一条 ALTER TABLE 为一个 500GB 的表添加 NOT NULL 约束 — 写入会发生什么情况？

常见问题解答

「在线迁移：ALTER TABLE 为何会加锁」课时是免费的吗？

是的 — 「在线迁移：ALTER TABLE 为何会加锁」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「在线迁移：ALTER TABLE 为何会加锁」这节课中我会学到什么？

了解哪些 ALTER TABLE 形式会获取 ACCESS EXCLUSIVE 锁并重写表，哪些形式只修改元数据你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「在线迁移：ALTER TABLE 为何会加锁」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy