PostgreSQL锁监控与死锁排查实践

概述目标：识别与解除锁等待与死锁，优化事务范围与索引以减少冲突，保障高并发下的稳定运行。适用：订单/库存等高并发写入与查询场景。核心与实战查看锁与等待：SELECT pid, usename, datname, relname, locktype, mode, granted FROM pg_locks l LEFT JOIN pg_class c ON l.relation=c.oid ORDER BY granted DESC; SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE state<>'idle' ORDER BY now()-xact_start DESC; 找出阻塞链：WITH blocked AS ( SELECT bl.pid AS blocked_pid, a.query AS blocked_query, bl.virtualtransaction AS blocked_vxid FROM pg_stat_activity a JOIN pg_locks bl ON a.pid=bl.pid WHERE NOT bl.granted ), blocking AS ( SELECT kl.pid AS blocking_pid, a.query AS blocking_query, kl.virtualtransaction AS blocking_vxid FROM pg_stat_activity a JOIN pg_locks kl ON a.pid=kl.pid WHERE kl.granted ) SELECT * FROM blocked JOIN blocking ON blocked.blocked_vxid=blocking.blocking_vxid; 死锁日志与设置：SHOW deadlock_timeout; -- 建议较短如1s ALTER SYSTEM SET deadlock_timeout='1s'; SELECT pg_reload_conf(); 事务优化建议：-- 缩短事务与锁持有时间；避免长事务与交叉更新顺序 EXPLAIN ANALYZE UPDATE orders SET status='PAID' WHERE id=$1; -- 确认走主键 示例终止长时间阻塞：SELECT pg_terminate_backend(<blocking_pid>); 索引与语句改写减少锁：-- 将范围扫描改为索引精确匹配，避免意外锁表 验证与监控实时监控：定期查询`pg_stat_activity`与`pg_locks`；记录阻塞时长与频率。日志：启用`log_lock_waits=on`与适当`deadlock_timeout`，在日志中追踪等待与死锁。变更评审：对涉及更新的大表进行`EXPLAIN`与索引检查，评估锁影响。常见误区长事务与批量更新未分批；导致锁范围扩大与等待堆积。未启用锁等待日志，排查困难；需开启`log_lock_waits`与合理超时。语句走错索引或全表扫描；应优化索引与过滤条件。结语通过系统视图与日志监控、事务与索引优化，可有效识别与缓解PostgreSQL锁冲突与死锁问题，提升并发稳定性。