AI 写的 SQL 把热点表锁了好几分钟 —— 排查与修复

你的事件表正以每秒 800 写入的速度运转。AI 写了一个”小清理脚本”——一条 UPDATE events SET status = 'archived' WHERE created_at < now() - interval '90 days'。你跑了。40 秒后告警炸锅:events 表写延迟从 5ms 飙到 12 秒,采集队列堆积,客户面板全在超时。查询还在跑,锁住三百万行,把所有并发写都堵在外面。AI 生成的 SQL 读起来很干净,因为它表达了意图,但它没建模的是:并发、锁粒度、以及在热点表上长事务的代价。

最快的修法(先做这个): 用 SELECT pg_cancel_backend(<pid>) 取消正在跑的语句先止血,然后把清理改成小批量(循环里用 LIMIT 5000 + FOR UPDATE SKIP LOCKED),别再一条巨型语句梭哈。本文剩下的内容就是教你怎么找到对的 pid、怎么改写、以及怎么让它不再复发。示例用 PostgreSQL(截至 2026 年 6 月,当前稳定版是 PostgreSQL 18);同样的思路也适用于 MySQL/InnoDB,只是语法不同。

你属于哪一类?

动手前先认清是哪种模式——不同原因修法不同。

你看到的现象	大概率原因	跳到
一条语句 active 30 秒以上,几百个被堵的会话	无边界 UPDATE/DELETE	原因 1、第 2 步
读没事,UPDATE 卡在你的事务后面	FOR UPDATE 不带 LIMIT	原因 2、第 4 步
锁数远超受影响行数;EXPLAIN 是 Seq Scan	缺索引	原因 3、第 3 步
idle in transaction 会话挂了几十秒	应用层处理塞进了 BEGIN/COMMIT	原因 4、第 5 步
低基数谓词上出现 relation 级(非 tuple 级)锁	锁升级压力	原因 5
源表和目标表同时被锁	writable CTE	原因 6
连 SELECT 都被堵	VACUUM FULL / 非 concurrent 的 REINDEX	原因 7、第 7 步

常见原因

按真实事故频次排序。

1. 无边界的 UPDATE / DELETE 一条搞定

AI 写一条语句覆盖几百万行。Postgres 每行加行锁、每页加 EXCLUSIVE 锁,任何并发写同样行的会话都被堵住。

如何识别:SELECT * FROM pg_stat_activity WHERE state = 'active' 看到你这条查询跑了 30 秒以上;pg_locks 里几百个 blocked session。

2. SELECT ... FOR UPDATE 不带 LIMIT

AI 以为 FOR UPDATE 让读”更安全”。没有 LIMIT 或键级 WHERE,它会锁住所有匹配谓词的行,常常等于整张表。更糟的是,裸 FOR UPDATE 拿的是最强的行锁——它还会挡住通过外键引用这些行的并发 INSERT。如果你并不改主键/外键列,SELECT ... FOR NO KEY UPDATE 是更轻的锁,能让那些引用型 insert 通过。

如何识别:表的读看起来还行,UPDATE(以及外键子表的 insert)排队。pg_locks 显示你的事务持有 FOR UPDATE / FOR NO KEY UPDATE 的 tuple 锁。

3. 缺索引导致的隐式全表扫描

UPDATE orders SET ... WHERE customer_email = ?,但 customer_email 没索引——全表扫描,即便只有 3 行命中,中间也锁了一堆。

如何识别:同一条 UPDATE 的 EXPLAIN ANALYZE 显示 Seq Scan;锁数远大于受影响行数。

4. 长事务把查询整个包起来

AI 把所有事都塞进 BEGIN/COMMIT:读数据 → 应用层处理 → 写回。如果”处理”那 90 秒在应用层,锁就被持有 90 秒。

如何识别:SELECT now() - xact_start FROM pg_stat_activity WHERE state = 'idle in transaction' 返回较大间隔。

5. 低基数列上的谓词导致锁升级

UPDATE events SET status = 'archived' WHERE status = 'pending',而 status 只有两种取值——半张表命中,数据库可能升级到表级锁。

如何识别:pg_locks 显示 ShareUpdateExclusive 或 Exclusive 锁,且对象是 relation 而不是 tuple 级别。

6. 写型 CTE 被计划器物化并防御性加锁

WITH old AS (DELETE FROM events WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM old——Postgres 把锁横跨两个操作。大结果集下,archive 的写也被堵。

如何识别:查询结构是 writable CTE;源表和目标表都有锁。

7. AI 给”性能优化”建议了 VACUUM FULL / 非 CONCURRENT 的 REINDEX

VACUUM FULL 拿的是 ACCESS EXCLUSIVE 锁——连读都阻塞。AI 偶尔会把它当成性能修复推过来。

如何识别:迁移或维护脚本里出现 VACUUM FULL <table>,整个应用故障期与之吻合。

开始之前

• 确认这条查询是不是真的还在跑。如果在跑,快速决策:让它跑完还是 kill。
• 看清楚目标表当前的写流量。写一次型表上的 10 秒锁没问题;1000/秒表上的 1 秒锁就是事故。
• 准备一个有真实数据量的非生产环境,用于测试改写后的版本。
• 在生产里跑任何危险操作前,先把 pg_cancel_backend / pg_terminate_backend 权限确认到位。

需要收集的信息

• AI 生成的原始 SQL。
• EXPLAIN (ANALYZE, BUFFERS) 的输出,最好对生产规模数据。
• SELECT pid, state, wait_event, query, now() - query_start AS dur FROM pg_stat_activity WHERE datname = current_database() ORDER BY dur DESC 的输出。
• 事故时的 SELECT * FROM pg_locks WHERE NOT granted 输出。
• 表大小与索引列表:\d+ <table>。
• 目标表的并发写速率。

分步修复

按”先止血,再重构”排序。

第 1 步:决定要不要 kill 正在跑的查询

如果应用延迟在涨,且就是这条查询造成的:

SELECT pid, query, now() - query_start AS dur
  FROM pg_stat_activity
  WHERE query LIKE 'UPDATE events%' AND state = 'active';

SELECT pg_cancel_backend(<pid>);
-- 若 10 秒内 cancel 无效:
SELECT pg_terminate_backend(<pid>);

pg_cancel_backend 是优雅取消(相当于 Ctrl-C 发个取消请求),pg_terminate_backend 是强制(断开连接并回滚)。先 cancel。注意:取消一条巨型 UPDATE/DELETE 会触发回滚,而回滚本身也要花时间、回滚期间还持有锁,所以延迟不会在你按下 cancel 的那一刻立刻恢复。如果连 pg_terminate_backend 都卡住,这个后端多半是卡在内核调用(磁盘 I/O)里,只能等,不要升级到 kill -9——那可能让整个实例崩溃。

第 2 步:把批量 UPDATE 改成分批

把:

UPDATE events SET status = 'archived'
  WHERE created_at < now() - interval '90 days';

改成:

DO $$
DECLARE
  rows_affected int := 1;
BEGIN
  WHILE rows_affected > 0 LOOP
    WITH batch AS (
      SELECT id FROM events
        WHERE created_at < now() - interval '90 days'
          AND status <> 'archived'
        ORDER BY id
        LIMIT 5000
        FOR UPDATE SKIP LOCKED
    )
    UPDATE events SET status = 'archived'
      WHERE id IN (SELECT id FROM batch);
    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

FOR UPDATE SKIP LOCKED 让并发写绕开你暂锁的行;pg_sleep 给计划器 / vacuum / 复制留余地。

第 3 步:在下次大批量前补上缺失的索引

如果 EXPLAIN 是顺序扫描:

CREATE INDEX CONCURRENTLY idx_events_created_at_status
  ON events(created_at) WHERE status <> 'archived';

partial index 对清理类查询特别合适:索引小、查找快,而且只覆盖还需要处理的那部分行。CONCURRENTLY 有两个坑:它不能放在 BEGIN/COMMIT 块里跑(单独执行);而且一旦构建失败——比如被取消,或第二遍扫描时撞上唯一性冲突——它会留下一个 INVALID 索引。用 \d <table> 或 SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; 检查,然后用 DROP INDEX CONCURRENTLY idx_events_created_at_status; 清掉再重试。另外,长事务也会拖慢这个构建,因为 CONCURRENTLY 要等它们结束。

第 4 步:FOR UPDATE 一律加边界

把:

SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

改成:

SELECT * FROM orders WHERE status = 'pending'
  ORDER BY id LIMIT 100 FOR NO KEY UPDATE SKIP LOCKED;

每次处理 100 条,跳过别人抢走的行,绝不会意外锁住整个 pending 集合。除非你真的要改这些行的主键/外键,否则用 FOR NO KEY UPDATE 而不是裸 FOR UPDATE——它是更弱的锁,不会挡住引用这些行的外键子表 insert。这就是经典的队列/worker 模式:SKIP LOCKED 让 N 个 worker 各拉互不相交的一批,零争用。

第 5 步:长处理移出事务

把:

BEGIN;
SELECT * FROM orders WHERE id = ? FOR UPDATE;
-- 应用侧 90 秒工作
UPDATE orders SET ... WHERE id = ?;
COMMIT;

改成:

BEGIN;
UPDATE orders SET processing_started_at = now() WHERE id = ? AND processing_started_at IS NULL;
COMMIT;
-- 应用侧 90 秒工作,不持有事务
BEGIN;
UPDATE orders SET ... WHERE id = ?;
COMMIT;

用 processing_started_at 的乐观认领替代长 FOR UPDATE。

第 6 步:在 session 上设超时

生产里跑任何 AI 生成的批量操作前:

SET statement_timeout = '30s';
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '60s';

超出预算就干净 abort,远好于把锁拽 10 分钟。这里真正救命的是 lock_timeout:它限定一条语句等待获取锁的最长时间,卡住的迁移会快速失败,而不是加入排队大军。上面的 SET 只对当前 session 生效;要让任何临时查询(包括粘贴进来的 AI 建议)都不可能把锁拽住太久,就在角色层设置,这样对每条连接都生效:

ALTER ROLE app_writer SET statement_timeout = '30s';
ALTER ROLE app_writer SET lock_timeout = '5s';
ALTER ROLE app_writer SET idle_in_transaction_session_timeout = '60s';

第 7 步:用 pg_repack 替代 VACUUM FULL

如果 AI 建议了 VACUUM FULL:

# 在线重组表;只在最后切换那一刻短暂持有 ACCESS EXCLUSIVE 锁
pg_repack -t events -d mydb

pg_repack 把表重建到一个影子副本上,期间原表仍可读可写,只在最后切换时拿一个很短的 ACCESS EXCLUSIVE 锁(几秒,与表大小无关)。pg_squeeze 是更新的替代品,用逻辑复制而非触发器,对主库压力更小。两者都是要安装的扩展。截至 2026 年 6 月,一个原生、非阻塞的 REPACK CONCURRENTLY 命令已在未来版本中预览(目标是 PostgreSQL 19),但尚未发布,所以现在仍需要扩展。大部分维护场景调一下 autovacuum 就够了——VACUUM FULL 在热点生产表上几乎从来不是正确答案,因为它拿的是完整的 ACCESS EXCLUSIVE 锁,连读都会被堵。

验证

• 改写后的查询 EXPLAIN ANALYZE 显示走索引、工作量有界。
• 在生产克隆 + 并发写负载下试跑,没有写延迟尖峰。
• 真实执行期间 pg_stat_activity 里这条查询单次运行时间从不超出 chunk 预算。
• 你的作业不会留下任何超过几秒的 idle in transaction 会话。
• 整个操作期间应用延迟面板保持平稳。

长期预防

• 在 CLAUDE.md / .cursorrules 里写明:“批量 UPDATE / DELETE 必须带 LIMIT + 循环。永远假设目标表有并发写。”
• 用 job 框架(Sidekiq、BullMQ、pgboss)管批量作业,而不是临时跑 SQL 脚本。
• 在角色层设 statement_timeout、lock_timeout、idle_in_transaction_session_timeout,这样哪怕 AI 不小心,锁也持不住太久。
• 内部约定:“任何影响超过 N 行的 DML 必须走 batch helper”,在 code review 里强制。
• 维护一份内部 SQL 模式手册,让 AI 引用:分批 update、SKIP LOCKED 队列、清理用的 partial index。
• Postgres 开 log_lock_waits = on、降低 deadlock_timeout,慢日志触发告警。

常见误区

• 高峰期跑 AI 那条”小清理查询”,理由是”看起来很小”。
• 觉得没索引的 WHERE”对小表也快”——dev 小,prod 巨大。
• 在主从架构下中途 kill 查询,没意识到锁已经复制到从库。
• 只在迁移脚本里设 statement_timeout,但临时 psql 会话里粘贴 AI 建议时没设。
• 以为 BEGIN; ... COMMIT; 更”安全”——它其实延长了锁持有时间。
• 在热点表上让 AI 生成 writable CTE,而没审视锁影响。

相关问题见 AI 迁移在生产 schema 上失败、AI 删掉了能跑的逻辑、AI 测试通过但功能其实坏了。本文涉及的锁模式,权威参考是 PostgreSQL 官方文档的 explicit locking 和 SELECT FOR UPDATE / SKIP LOCKED。

FAQ

Q:AI 那条 SQL 看起来跟 Stack Overflow 上的例子一模一样,为什么在我这就翻车?

Stack Overflow 例子大多是玩具库。生产表行数是它的 100 倍,还有并发写、还有复制。同一条 SQL 在小规模安全,在大规模就是灾难。

Q:加个 LIMIT 1000 反复跑行不行?

那其实就是上面分批方案的简化版,再加上循环和 SKIP LOCKED。一次性的偶尔为之没问题;清理类作业必须走脚本化循环。

Q:FOR UPDATE SKIP LOCKED 为什么重要?

没有 SKIP LOCKED,分批 worker 会卡在别人正在锁的同一批行后面。SKIP LOCKED 让 worker 绕过竞争行继续前进——并行作业里不可或缺。

Q:我那条批量是 ORM 生成的,不是 AI,同一套修法吗?

适用。ActiveRecord update_all、Sequelize bulkUpdate、Prisma updateMany 默认都是一条语句批量写。在 ORM 层配置 batch,或直接改写调用。

Q:我取消了查询,延迟却还是很高,为什么?

取消一条大 UPDATE/DELETE 会触发回滚,而回滚同样要逐行处理改过的行,期间还持有锁,所以恢复不是瞬间的。要等这个后端从 pg_stat_activity 里消失再判定它真的结束了。这正是要分批的有力理由:5000 行的一批毫秒级就回滚完了。

Q:FOR UPDATE 和 FOR NO KEY UPDATE 该用哪个?

除非你要改主键、或改被外键引用的列,否则用 FOR NO KEY UPDATE。它是更弱的锁,照样能防止两个 worker 抢同一行,但不会挡住通过外键引用这些行的子表 insert——在繁忙表上更少假冲突。

Q:怎么查到是谁堵了谁,而不只是”被堵了”?

把 pg_locks 和 pg_stat_activity join 起来,或直接用 blocking-pid 辅助函数:SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;。blocked_by 数组会告诉你该先 cancel 哪个后端。

标签: #排查 #AI 编程 #sql #数据库 #locks

相关文章

常见问题解决库

AI 加了一条绕过鉴权中间件的路由 —— 排查与修复

AI 在鉴权路由组之外注册了新接口,把内部数据无防护暴露出去。最快的修法:把路由迁到鉴权 router 下,并在数据层(而非中间件)强制 auth。

2026/05/24 #排查 #AI 编程

常见问题解决库

AI 编造了根本不存在的 API 签名 —— 排查与修复

AI 调用了一个并不存在的方法:参数顺序错、选项名瞎编、返回类型臆造。对照真实 .d.ts 定位,并用当前文档锚定后续生成。

2026/05/24 #排查 #AI 编程

常见问题解决库

AI 生成的迁移本地能跑、上生产就炸:修复指南

AI 写的迁移在空 dev 库里干净通过,到生产却卡死、触发约束错误或 NULL 报错。用生产克隆测试加几套安全 SQL 模式彻底解决。

2026/05/24 #排查 #AI 编程

常见问题解决库

AI 无视 lint 报错继续用废弃语法 —— 排查与修复

AI 生成的代码用了废弃 API,看到 lint 报错改了一处,下一个文件又把废弃写法塞回来。把禁令写进 .cursor/rules,并让 ESLint 在 CI 里硬性报 error。

2026/05/24 #排查 #AI 编程

常见问题解决库

AI 在 pnpm / Yarn 项目里跑 npm 命令(锁文件修复)

AI 在 pnpm 仓库里跑 npm install,生成 package-lock.json,破坏锁文件契约、把 CI 弄崩。用 devEngines/corepack 加 preinstall 守卫锁死包管理器。

2026/05/24 #排查 #AI 编程

常见问题解决库

AI 建议了过时依赖：快速修复

AI 推荐了一个已停更的 npm/PyPI 包。用 npm view 三秒判定、换成现代替代、再让它不再复发。

2026/05/21 #AI 编程 #排查