会话和锁

会话和锁

目录

• 会话和锁
  • 会话查询
    • 查看当前所有会话概览
    • 只查 活跃 SQL（state = 'active'）
    • 查 长时间空闲事务（可能持锁未释放）
    • 杀死某个会话（慎用）
    • 查看当前窗口连接到服务器进程PID
  • 锁查询
    • 1.找出阻塞源头（谁持锁不释放）
    • 2.查看某张表上所有锁
    • 3.计算锁等待时长（秒）
    • 查询所有的会话信息：

会话查询

在 PostgreSQL 中，会话（session）信息全部集中在视图 pg_stat_activity 里。一条 SQL 就能查到你关心的所有字段，下面给出“常用字段 + 示例查询 + 典型场景”

字段	含义	示例值
pid	后端进程号	12345
usename	连接的数据库用户	app_user
application_name	应用名（JDBC/Go/业务可自定义）	my_api
client_addr	客户端 IP	10.0.0.21
client_port	客户端端口	45678
datname	当前数据库	prod
state	会话状态	active / idle / idle in transaction / disabled
query	正在执行或最后一条 SQL	SELECT * FROM t WHERE …
query_start	当前 SQL 开始时间	2024-06-12 14:32:10
state_change	最近一次状态变化时间	2024-06-12 14:32:12
wait_event_type / wait_event	事件等待类型+事件名	Lock / transactionid
backend_start	会话建立时间	2024-06-12 14:00:00
backend_type	后端类型	client backend / autovacuum / checkpointer

常用查询模板

查看当前所有会话概览

SELECT pid, usename, datname, state, query_start, left(query, 80) AS query_snippet
FROM pg_stat_activity
ORDER BY query_start;

只查 活跃 SQL（state = 'active'）

SELECT pid, usename, datname, query_start, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

查 长时间空闲事务（可能持锁未释放）

SELECT pid, usename, datname, query_start, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '30 minutes';

杀死某个会话（慎用）

-- 先查 pid，再执行
SELECT pg_terminate_backend(12345);

查看当前窗口连接到服务器进程PID

select pg_backend_pid();

锁查询

pg_locks 是 PostgreSQL 的系统视图，实时展示 当前所有会话已持有或正等待的锁。理解它的字段就能快速定位“谁堵了谁”。下面按官方文档顺序给出中文说明、取值示例与排查技巧。

• 字段说明：
  | 字段名 | 类型 | 中文说明 | 典型值 / 备注 |
  | ----------------- | ----------- | --------------------- | -------------------------------------------------------------------------------------------- |
  | locktype | text | 锁的粒度类别 | relation / tuple / page / transactionid / virtualxid / advisory / object / extend / frozenid |
  | database | oid | 数据库 OID | 0 表示共享对象，非 0 时使用 oid = 16384 对应具体库 |
  | relation | oid | 表/索引/序列 OID | 与 pg_class.oid 关联；非 relation 锁为 0 |
  | page | int4 | 页号 | 仅 locktype = page 时有效 |
  | tuple | int2 | 页内行号 | 仅 locktype = tuple |
  | classid | oid | 系统 catalog 类 OID | locktype = object 时使用 |
  | objid | oid | 系统 catalog 对象 OID | 同上 |
  | objsubid | int4 | 子对象号 | 如列号、过程参数序号；无子对象时为 0 |
  | transactionid | xid | 事务号 | locktype = transactionid |
  | virtualxid | text | 虚拟事务号 | 形如 4/123，会话内部唯一 |
  | pid | int4 | 持有或等待的后端进程 ID | 与 pg_stat_activity.pid 关联 |
  | mode | text | 锁模式 | AccessShare / RowExclusive / Exclusive … |
  | granted | bool | 是否已获取锁 | true = 已持有，false = 正在等待 |
  | fastpath | bool | 是否走“快速路径” | true 表示通过共享锁表快速授予 |
  | waitstart | timestamptz | 开始等待时间 | 仅当 granted = false 时非空，用于计算等待时长 |

1.找出阻塞源头（谁持锁不释放）

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_locks.mode    AS blocked_mode,
       blocking_locks.mode   AS blocking_mode,
       blocked_activity.query  AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN 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_stat_activity blocking_activity  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

2.查看某张表上所有锁

SELECT l.pid, l.mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE relation = 't1'::regclass
ORDER BY granted;

3.计算锁等待时长（秒）

SELECT pid, mode, granted,
       extract(epoch FROM (now() - waitstart)) AS wait_sec
FROM pg_locks
WHERE granted = false;

查询所有的会话信息：

select
    a.pid,
    max(a.datname) as datname,
    max(a.application_name) as application_name,
    max(a.usename) as usename,
    max(a.client_addr) as client_addr,
    max(a.client_hostname) as client_hostname,
    max(a.client_port) as client_port,
    max(a.backend_start) as backend_start,
    max(a.xact_start) as xact_start,
    to_char((now()- max(a.xact_start))::interval,'HH24:MI:SS') as xact_duration,
    max(a.query_start) as query_start,
    max(a.wait_event_type) as wait_event_type,
    max(a.wait_event) as wait_event,
    max(a.state) as state,
    max(a.state_change) as state_change,
    max(a.backend_type) as backend_type,
    max(a.query) as query,

    pg_blocking_pids(a.pid) as blocking_pids
from pg_stat_activity a

group by a.pid