postgresql-记一次因数据库锁导致的接口超时问题排查

背景与现象#

线上系统采用腾讯云云原生结构,请求的链路如下：

client -> TencentCLB -> TencentIngress -> TencentPod(APP,python unicorn)

一次升级后，发现系统中某个接口请求后会被hang主，但其它接口正常。使用kubectl log查看对应Pod的日志，看不到任何关于该接口的请求记录。

初步定位：接口无响应、日志缺失#

通过浏览器开发者工具排查，确认是/api/workspaces接口被挂住了，无任何响应。使用 kubectl port-forward直连API Pod。以绕开本地代理、公网边缘节点和内网 CLB，对同一个 Pod 发送两次请求：

请求	结果
不带 cookie	403，0.14s 秒回
带有效 cookie	挂死，20s 超时，无响应
为什么 `kubectl logs` 看不到日志？
因为 uvicorn 的 access log 是在响应完成时才打印的。
带 cookie 的请求在后端一直挂住、无法返回，因此不会产生对应的 access log，所以 Pod 日志中看不到这条请求。

锁定数据库锁等待#

进一步排查发现，问题出在数据库层：

/api/users/me 实际执行的是 SELECT FROM "user"，可以秒回。
/api/workspaces 管理员路径最终执行的是裸 SELECT FROM workspace，没有复杂关联、网络或 Redis 依赖，却一直挂死。一条普通 SELECT 在单表上无限期挂住，最大的可能是这张表被 ACCESS EXCLUSIVE 锁阻塞，只有 ACCESS EXCLUSIVE 级别的锁会直接阻塞纯 SELECT。 ACCESS EXCLUSIVE 通常来自：
ALTER TABLE
CREATE INDEX（非 CONCURRENTLY）
VACUUM FULL
LOCK TABLE
或一个忘记提交、卡住的迁移事务结合升级会进行 alembic 迁移，基本可以锁定是迁移/DDL 事务卡在 workspace 表上未释放锁。连接Postgresql，执行下面的语句：

-- 查看当前活跃事务，谁被阻塞、卡了多久、被谁阻塞
SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       now()-a.xact_start AS xact_age, left(a.query,120) AS query,
       pg_blocking_pids(a.pid) AS blocked_by
FROM pg_stat_activity a
WHERE a.datname='aimdrdb' AND a.state <> 'idle'
ORDER BY xact_age DESC NULLS LAST;

-- 直接看 workspace 表上的锁；
-- granted=false 的就是被挡住的 SELECT
SELECT l.pid, l.mode, l.granted, a.state,
       now()-a.xact_start AS xact_age, left(a.query,120) AS query
FROM pg_locks l JOIN pg_class c ON c.oid=l.relation
LEFT JOIN pg_stat_activity a ON a.pid=l.pid
WHERE c.relname='workspace' ORDER BY l.granted;

通过查询发现一个长期 idle in transaction 的会话持有 AccessShareLock，另一个 DDL 会话正在排队等待 AccessExclusiveLock，后面还有大量 API 的 SELECT FROM workspace 被阻塞。

锁等待链分析#

锁等待情况整理如下：

PID	锁	granted	状态 / 时长	在干什么
28983	AccessShare	✅ 已持有	idle in transaction，2 天 11 小时	一个 BEGIN 后没提交、被遗弃的事务（最后一句查 security_event_log），但它早先在事务里摸过 workspace，一直攥着 workspace 的AccessShareLock 不放
56411	AccessExclusive	❌ 排队中	active，9 小时 11 分	`DROP TABLE IF EXISTS case_agg_windows` —— 要删这张表得先拿workspace 的 AccessExclusiveLock（case_agg_windows 有指向 workspace的外键），但被 28983 挡住，一直排队
上百个	AccessShare	❌	active，几秒~1.5 小时	全是 SELECT … FROM workspace（你的 /api/workspaces + consumer 的 tenant 查询），全堵在 56411 后面全部排队`
锁等待链如下：

28983(持 AccessShareLock, idle in transaction 2.5天)
  ← 阻塞 ← 56411(等待 AccessExclusiveLock, DROP TABLE)
  ← 阻塞 ← 全部 SELECT FROM workspace

为什么普通 SELECT 也会被堵死？#

正常情况下，SELECT 需要的 AccessShareLock 与 28983 持有的 AccessShareLock 是兼容的，本应直接通过。但中间插入了一个正在排队的 AccessExclusiveLock。 PostgreSQL 为了避免强锁被不断到来的弱锁“饿死”，会让后续所有新的读请求排在等待中的强锁后面。因此，后面的上百个 SELECT 全部被 56411 阻塞，而 56411 又被 28983 阻塞，最终导致所有带 cookie 的 /api/workspaces 请求挂死。

处置步骤#

生产库处置顺序很重要，按以下顺序执行v之后恢复服务。

第一步：先终止排队的 DDL，立即恢复服务#

SELECT pg_terminate_backend(56411);

56411 离开等待队列后，积压的大量 SELECT FROM workspace 会立即通过。 DROP TABLE 属于清理性操作，回滚没有数据损失，之后可以重新执行。

第二步：终止长期持锁的僵尸事务#

SELECT pg_terminate_backend(28983);

28983 是一个 idle in transaction 事务，内部只有 SELECT，回滚无副作用。
只终止 56411 就能立刻恢复服务，但如果不清除 28983，后续再有 workspace 上的 DDL，仍然会重演同样的阻塞。

第三步：必要时重启 API#

如果个别 API 连接仍然卡住，可以在解锁后重启：

kubectl rollout restart deploy/aimdr-api -n aimdr

顺序很重要：必须先解锁，再重启。
如果先重启，新产生的请求仍然会被未释放的锁阻塞。

善后与长期建议#

1. 排查 28983 的来源#

28983 是一个持续 2.5 天的 idle in transaction 事务，事务内查询过 security_event_log 和 workspace，但一直未提交。
这通常来自：

崩溃的脚本
手动 psql 操作后忘记提交
连接泄漏这是本次故障真正的根源。

2. 在低峰期重跑迁移 DDL#

DROP TABLE case_agg_windows 仍需要在低峰窗口重新执行，以完成迁移。
建议给迁移会话增加锁超时：

SET lock_timeout = '5s';

这样 DDL 拿不到锁时会快速失败，而不是长时间排队，再次阻塞全表读请求。

3. 设置空闲事务超时#

在数据库侧设置：

SET idle_in_transaction_session_timeout = '10min';

或写入实例配置，例如 5–15 分钟。这样可以自动回收长期空闲的事务，避免一个遗忘提交的连接拖垮整个业务。

7. 总结#

本次接口超时的根因不是死锁，而是锁等待队列阻塞：

一个长期未提交的事务持有 workspace 的 AccessShareLock
一个 DDL 请求 AccessExclusiveLock 被它阻塞
后续所有普通 SELECT 又排在 DDL 后面最终表现为 /api/workspaces 接口大量超时。
处置时先终止排队的 DDL，再清理僵尸事务，最后重启残留连接即可恢复。

找到那个 blocking pid：如果是没跑完/卡死的迁移或忘了 commit 的事务 → SELECT pg_terminate_backend(<blocking_pid>);（或先 pg_cancel_backend）。
锁一释放，积压的 /api/workspaces 立刻恢复，后台就能进了。
已经挂死的 api 连接可 kubectl rollout restart deploy/aimdr-api -n aimdr 清一下——注意顺序：先解锁再重启，不然重启后新请求照样挂在锁上。

参考文档#

postgresql-postgresql中的锁
postgresql-postgresql中检查慢查询的原因