postgres-lock-wait

PostgreSQL 锁等待

现象

业务写入超时或长时间不返回，pg_stat_activity 中出现大量 wait_event_type = Lock；应用日志报 canceling statement due to statement timeout 或死锁。

快速定位

psql -c "SELECT pid, wait_event_type, wait_event, state, query_start, query
FROM pg_stat_activity WHERE wait_event_type = 'Lock';"

查看阻塞链：

psql -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
blocked.query AS blocked_query, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;"

常见根因

• 长事务持有行锁，未提交或未回滚。
• 批量 UPDATE/DELETE 锁范围过大。
• 外键、索引创建或 DDL 阻塞写入。
• 应用连接泄漏，事务一直开着。
• 死锁检测后事务被终止。

修复

• 终止阻塞会话：SELECT pg_terminate_backend(<PID>);
• 缩短事务，避免在事务中调用外部服务。
• 批量更新分批提交，减少锁持有时间。
• DDL 在低峰期执行，使用 CONCURRENTLY 建索引。
• 设置 statement_timeout 和 lock_timeout。
• 监控长事务和锁等待，设置告警。

相关命令

• psql — pg_stat_activity、pg_locks
• pg_terminate_backend — 终止会话
• pg_stat_statements — 慢查询
• pgbouncer — 连接池