PostgreSQL锁阻塞排查需先通过pg_locks与pg_stat_activity定位等待会话，识别idle in transaction等异常状态，再用pg_cancel_backend或pg_terminate_backend终止阻塞进程，并可聚焦表级锁分析及启用log_lock_waits持续监控。
一、定位正在等待锁的会话
当业务出现明显延迟或SQL长时间不返回时，很可能是某个会话正被锁阻塞。PostgreSQL通过pg_locks与pg_stat_activity两个系统视图暴露锁状态，可快速识别出处于等待状态的进程。
1、连接数据库执行以下查询：
SELECT blocked_activity.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocked_activity.query AS blocked_query,
blocking_activity.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
2、检查结果中blocked_pid列非空且granted为f的记录，即为被阻塞的会话。
3、确认blocking_pid对应会话的state字段是否为idle in transaction，该状态表明事务已执行完但未提交或回滚，是常见阻塞源。
二、识别持有锁但未释放的事务
阻塞源头往往是一个已获取锁却长期未结束的事务。这类事务可能因应用逻辑缺陷、网络中断或人为误操作而滞留，需主动定位其执行语句与生命周期。
1、执行以下查询获取所有持锁会话详情：
SELECT pid, usename, datname, client_addr, application_name, state,
now() - backend_start AS backend_duration,
now() - xact_start AS transaction_duration,
now() - query_start AS query_duration,
substring(query FROM 1 FOR 80) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
ORDER BY transaction_duration DESC;
2、重点关注state = 'idle in transaction'且transaction_duration超过预期阈值（如5分钟）的行。
3、对可疑pid执行：
SELECT pg_get_backend_pid(blocking_pid); —— 验证进程活跃性。
三、终止异常阻塞会话
在确认阻塞源无业务影响后，可通过系统函数强制中断其事务，释放持有的所有锁。该操作不可逆，需谨慎评估上下文。
1、使用pg_cancel_backend终止当前查询但保留会话：
SELECT pg_cancel_backend(blocking_pid);
2、若会话仍处于idle in transaction且未响应，改用pg_terminate_backend彻底断开连接：
SELECT pg_terminate_backend(blocking_pid);
3、执行后立即验证pg_locks中对应pid的记录是否消失，且原blocked_pid会话恢复运行。
四、检查特定表的锁冲突详情
当明确知道某张业务表（如orders、inventory）出现卡顿，可聚焦分析该表上的锁分布，避免全库扫描带来的干扰。
1、获取表OID：
SELECT oid, relname FROM pg_class WHERE relname = 'your_table_name' AND relkind = 'r';
2、查询该表上所有未授予的锁及其关联进程：
SELECT c.relname AS table_name,
l.mode AS lock_mode,
l.pid AS blocking_pid,
a.query AS blocking_query,
>a.state AS blocking_state,
now() - a.query_start AS wait_duration
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
AND c.oid = your_table_oid;
3、结果中lock_mode为AccessExclusiveLock通常对应DDL操作（如ADD COLUMN），需优先排查。
五、启用锁等待日志进行持续监控
被动排查效率低，主动记录锁等待行为可帮助复盘历史问题并建立预警机制。PostgreSQL支持在配置层开启细粒度锁日志输出。
1、编辑postgresql.conf，添加或修改以下参数：
log_lock_waits = on
deadlock_timeout = 1s
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '
2、确保日志级别不低于log：
log_min_messages = log
3、执行配置重载命令：
SELECT pg_reload_conf();
4、此后所有超过1秒的锁等待将被写入日志文件，包含阻塞进程PID、等待锁类型、持锁进程PID及原始SQL片段。
