你的事件表正以每秒 800 写入的速度运转。AI 写了一个”小清理脚本”——一条 UPDATE events SET status = 'archived' WHERE created_at < now() - interval '90 days'。你跑了。40 秒后告警炸锅:events 表写延迟从 5ms 飙到 12 秒,采集队列堆积,客户面板全在超时。查询还在跑,锁住三百万行,把所有并发写都堵在外面。AI 生成的 SQL 读起来很干净,因为它表达了意图,但它没建模的是:并发、锁粒度、以及在热点表上长事务的代价。
常见原因
按真实事故频次排序。
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,它会锁住所有匹配谓词的行,常常等于整张表。
如何识别:表的读看起来还行,UPDATE 排队。pg_locks 显示你的事务持有大量 row-exclusive 锁。
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 优雅(SIGINT),pg_terminate_backend 强制(SIGTERM)。先 cancel。
第 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 对清理类查询特别合适——索引小、查找快、清理干净后自然消失。
第 4 步:FOR UPDATE 一律加边界
把:
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;改成:
SELECT * FROM orders WHERE status = 'pending'
ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED;每次处理 100 条,跳过别人抢走的行,绝不会意外锁住整个 pending 集合。
第 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 分钟。
第 7 步:用 pg_repack 替代 VACUUM FULL
如果 AI 建议了 VACUUM FULL:
# 在线重组表,不需要 ACCESS EXCLUSIVE
pg_repack -t events -d mydbpg_repack 在线重建表。大部分维护场景调一下 autovacuum 就够了,VACUUM FULL 在生产几乎从来不是正确答案。
验证
- 改写后的查询
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 测试通过但功能其实坏了。
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,或直接改写调用。