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}
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;预期输出
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' 时,通常已经进入事务之间的等待。

读懂 relation 与 transactionid
relation 指向表、索引等 relation 对象,常用于解释表级锁竞争;在当前数据库中可以用 relation::regclass 转成可读名称。transactionid 则表示锁目标是某个事务 ID,而不是某张表。
这一区分对行锁排障尤其重要。PostgreSQL 的行级读写锁记录在被锁行中,并不会简单地把“谁锁了哪一行”完整展开成两条 pg_locks 记录;发生冲突时,等待者常表现为等待持锁事务的事务 ID。:contentReference[oaicite:1]{index=1}
因此,看到 transactionid 不要误判成“与业务表无关”。它很可能正是另一事务修改同一行后,当前事务必须等待对方结束的间接证据。
把事务 ID 映射回持锁者
当等待记录是 transactionid 时,可以寻找对同一事务 ID 已获授予的锁,再关联等待与持锁会话的 SQL。下面的查询只处理事务 ID 锁,避免把其他锁目标混在一起。
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;预期输出
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 文本,还要检查当前账号是否具备相应的统计信息查看权限。