PostgreSQL 锁机制与事务阻塞排查实战-php教程-PHP中文网

postgresql锁阻塞排查需先通过pg_locks与pg_stat_activity定位等待会话，识别idle in transaction等异常状态，再用pg_cancel_backend或pg_terminate_backend终止阻塞进程，并可聚焦表级锁分析及启用log_lock_waits持续监控。

postgresql 锁机制与事务阻塞排查实战 - php中文网

一、定位正在等待锁的会话

当业务出现明显延迟或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），需优先排查。