PostgreSQL / 系统课时

用 pg_locks 还原锁竞争

用 pg_locks 还原锁竞争

SQL 卡住时,先别从“哪张表最忙”猜根因。pg_locks 能把当前后端持有和等待的锁拆成可查询记录,再结合 pg_stat_activity 识别等待对象、锁模式与会话状态,最终把一次看似模糊的行锁问题还原到具体事务。

学习目标

  • 能从 granted、locktype、mode 和 waitstart 识别真正的锁等待。
  • 能区分 relation 与 transactionid 锁目标,理解行级竞争为何常以事务 ID 等待出现。
  • 能把等待中的事务 ID 锁映射回持锁会话。

从等待记录定位锁对象

pg_locks 的一行表示某个后端对一个可锁对象持有或请求的一种锁。排障时先看 granted=false:它表示请求尚未获得;再关联 pg_stat_activity,就能看到当前 SQL 和等待事件。:contentReference[oaicite:0]{index=0}

sql
SELECT
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation,
    l.transactionid,
    l.waitstart,
    left(a.query, 80) AS query
FROM pg_locks AS l
JOIN pg_stat_activity AS a
  ON a.pid = l.pid
WHERE a.datname = current_database()
  AND l.granted = false
ORDER BY l.waitstart NULLS LAST, a.pid;

预期输出

text
 pid          | state  | wait_event_type | wait_event    | locktype      | mode      | granted | relation | transactionid | query
--------------+--------+-----------------+---------------+---------------+-----------+---------+----------+---------------+---------------------------
 <PID_WAITER> | active | Lock            | transactionid | transactionid | ShareLock | f       |          | <XID>         | UPDATE accounts SET ...

实际 PID、事务 ID 与时间会不同。关键是先回答“哪个请求没拿到锁、它在等什么对象”。当 wait_event_type='Lock' 且 locktype='transactionid' 时,通常已经进入事务之间的等待。

行级竞争在 pg_locks 中的间接表现

读懂 relation 与 transactionid

relation 指向表、索引等 relation 对象,常用于解释表级锁竞争;在当前数据库中可以用 relation::regclass 转成可读名称。transactionid 则表示锁目标是某个事务 ID,而不是某张表。

这一区分对行锁排障尤其重要。PostgreSQL 的行级读写锁记录在被锁行中,并不会简单地把“谁锁了哪一行”完整展开成两条 pg_locks 记录;发生冲突时,等待者常表现为等待持锁事务的事务 ID。:contentReference[oaicite:1]{index=1}

因此,看到 transactionid 不要误判成“与业务表无关”。它很可能正是另一事务修改同一行后,当前事务必须等待对方结束的间接证据。

把事务 ID 映射回持锁者

当等待记录是 transactionid 时,可以寻找对同一事务 ID 已获授予的锁,再关联等待与持锁会话的 SQL。下面的查询只处理事务 ID 锁,避免把其他锁目标混在一起。

sql
WITH waiting_xid AS (
    SELECT pid, transactionid, mode, waitstart
    FROM pg_locks
    WHERE locktype = 'transactionid'
      AND granted = false
)
SELECT
    w.pid AS waiting_pid,
    h.pid AS holder_pid,
    w.transactionid AS xid,
    w.mode AS waiting_mode,
    h.mode AS holder_mode,
    w.waitstart,
    left(wa.query, 60) AS waiting_query,
    left(ha.query, 60) AS holder_query
FROM waiting_xid AS w
JOIN pg_locks AS h
  ON h.locktype = 'transactionid'
 AND h.transactionid = w.transactionid
 AND h.granted = true
LEFT JOIN pg_stat_activity AS wa
  ON wa.pid = w.pid
LEFT JOIN pg_stat_activity AS ha
  ON ha.pid = h.pid
ORDER BY w.waitstart NULLS LAST;

预期输出

text
 waiting_pid  | holder_pid   | xid   | waiting_mode | holder_mode   | waiting_query           | holder_query
--------------+--------------+-------+--------------+---------------+-------------------------+------------------------
 <PID_WAITER> | <PID_HOLDER> | <XID> | ShareLock    | ExclusiveLock | UPDATE accounts SET ... | UPDATE accounts SET ...

这一步已经能回答“谁在等谁的事务”,但不能可靠判断多级阻塞中的最上游根因。如果持锁者自己也在等另一个会话,应继续沿实际阻塞关系向上追踪,而不是只凭这一对 PID 就处置。

避免常见误判

granted=true 只表示锁已获得,不等于它正在阻塞别人;必须与冲突请求结合判断。反过来,granted=false 是等待证据,但锁目标不一定直接带业务表名。

单看 tuple 为空也不能证明“没有行锁竞争”。排查 UPDATE、DELETE 或 SELECT ... FOR UPDATE 卡住时,应把 transactionid、等待事件、当前 SQL 与事务持续时间一起判断。若看不到其他用户的 SQL 文本,还要检查当前账号是否具备相应的统计信息查看权限。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

用 pg_locks 还原锁竞争

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通