页级锁与死锁

很多从 MySQL 迁移过来的开发者会习惯性地问：「PostgreSQL 的页级锁（Page Lock）、间隙锁（Gap Lock）怎么用？」答案是——PostgreSQL 里根本没有这两种锁。它的行级锁只有四种模式，不存在「锁一个范围」的间隙锁，也不存在「锁一整页」的页级锁。

但「页」这个概念在 PostgreSQL 里并没有消失，只是换了一种存在形式：它以缓冲区锁（Buffer Lock）和轻量级锁（LWLock, Lightweight Lock）的面貌出现在存储层，成为高并发热点更新时的真实瓶颈。本页先澄清「没有页级锁」这件事，再讲清 PostgreSQL 里真正的页级争用从哪来、怎么观察，最后把死锁（Deadlock）的等待环定位方法一并讲透。

前置知识

阅读本节前，建议先了解：行级锁（FOR UPDATE / FOR SHARE）、表级锁（ACCESS SHARE / EXCLUSIVE）

基础概念

PostgreSQL 与 MySQL 的锁模型对比

先把最容易混淆的地方说清楚。两者对「页」和「间隙」的处理完全不同：

维度	PostgreSQL	MySQL InnoDB
行级锁模式	4 种：FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE	记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）
间隙锁	没有	REPEATABLE READ 下默认使用，防止幻读
页级锁	没有（用户不可见的锁模式）	有页锁，粒度介于表锁与行锁之间
范围锁	通过 SERIALIZABLE 的谓词锁（Predicate Lock）实现，且不阻塞读	通过临键锁实现，会阻塞
存储层内部锁	有：LWLock、buffer pin	有：latch、mutex
行锁存储位置	元组头（tuple header）的 xmax 字段	独立的锁结构 / 位图（bitmap）
加锁开销	写入元组头即可，几乎零额外内存	需要维护锁表或位图

关键结论

PostgreSQL 通过 MVCC（多版本并发控制） 而不是间隙锁来避免幻读：读操作永远读快照，不会阻塞写。所以在 PostgreSQL 中永远不要尝试「用间隙锁把某个范围锁住」——没有这个功能；需要范围保护时，正确做法是显式锁一张「范围表」，或者使用 SERIALIZABLE 隔离级别。

存储层三类锁：行锁、LWLock、Buffer Pin

用户在 SQL 层面能感知的只有「表级锁」和「行级锁」，但存储层还有两套完全不同的机制在运转：

• 行级锁（Row-Level Lock）：写在元组头里，用户可见（通过 pg_locks 的 transactionid 间接观察），属于「重量级锁（Heavyweight Lock）」体系。
• LWLock（轻量级锁）：保护共享内存中的数据结构，不写磁盘、不在 pg_locks 里，只在 pg_stat_activity.wait_event_type = 'LWLock' 中可见。
• Buffer Pin（缓冲区钉住）：保护的是「这一页正在被读」，防止缓冲区被换出，等待时 wait_event_type = 'BufferPin'。

用户视角                       内核视角
─────────────────────────     ─────────────────────────────
LOCK TABLE           ─┐
SELECT ... FOR UPDATE ─┼──►  重量级锁 (pg_locks 可见)
UPDATE / DELETE       ─┘

                              LWLock (共享内存结构保护)
                              ├─ buffer_mapping  映射表锁
                              ├─ buffer_content  缓冲区内容锁
                              ├─ buffer_io       缓冲 I/O 锁
                              └─ WALInsert / WALWrite ...

                              Buffer Pin (页面引用计数)

行锁在数据页上的物理形态

PostgreSQL 的行锁不占用独立的锁表，而是直接写进 堆页（Heap Page） 里每一行的元组头。一个 8 KB 的数据页结构如下：

┌────────────────────────────────────────────────────────────┐
│ 页头 PageHeaderData (24 字节)                                │
│   ├─ pd_lsn        最近修改的 WAL 位置                       │
│   ├─ pd_checksum   页校验和                                  │
│   ├─ pd_lower / pd_upper   空闲空间边界                       │
│   └─ pd_special    B-tree 等特殊页用                          │
├────────────────────────────────────────────────────────────┤
│ 行指针数组 ItemIdData[] → 指向元组的偏移与长度                 │
├────────────────────────────────────────────────────────────┤
│                    ← 空闲空间 (Free Space) →                 │
├────────────────────────────────────────────────────────────┤
│ 元组 2  HeapTupleHeaderData                                 │
│   ├─ t_xmin  = 100        （插入它的事务 ID）                 │
│   ├─ t_xmax  = 205        （删除/锁定它的事务 ID）★行锁就写这里 │
│   ├─ t_infomask           （标记位，见下）                    │
│   └─ t_hoff / 数据 ...                                      │
├────────────────────────────────────────────────────────────┤
│ 元组 1  HeapTupleHeaderData                                 │
│   ├─ t_xmin  = 100                                          │
│   ├─ t_xmax  = 0          （未被锁、未被删）                   │
│   └─ 数据 ...                                               │
└────────────────────────────────────────────────────────────┘

元组头里的 t_infomask 会用几个标记位来区分「xmax 是删除还是加锁」：

标记位	含义
HEAP_XMAX_LOCK_ONLY	xmax 只表示加锁，不代表行被删除（SELECT ... FOR UPDATE 触发的就是它）
HEAP_XMAX_IS_MULTI	该行的 xmax 指向一个多事务 ID（MultiXact），用于多个事务共享锁同一行
HEAP_XMAX_KEYSHR_LOCK	xmax 是 FOR KEY SHARE 类型的锁
HEAP_XMAX_EXCL_LOCK	xmax 是排他类型的锁

为什么行锁「不占空间、却又要落盘」

写 xmax 只是修改页内的几个字节，但记录这些修改的 WAL 必须落盘。所以一次 SELECT ... FOR UPDATE 虽然不产生新版本，却会产生 WAL 日志——这是很多人没意识到的一点：读加锁也会写日志。

语法与用法

观察页级争用（LWLock / BufferPin）

存储层的等待没有专门的 SQL 语法，只能通过 pg_stat_activity 的 wait_event_type / wait_event 两列观察：

SELECT pid, wait_event_type, wait_event, ...
FROM pg_stat_activity
WHERE wait_event_type IN ('LWLock', 'BufferPin', 'Lock', 'IO');

-- 找出当前正在等待 LWLock 的所有会话，以及它们在等哪一类共享内存结构
SELECT pid,
       usename,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS query_age,
       left(query, 60)      AS query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
ORDER BY query_start;

 pid  | usename |        state        | wait_event_type |   wait_event   |  query_age  |           query
------+---------+---------------------+-----------------+----------------+-------------+----------------------------
 8421 | app     | active              | LWLock          | buffer_content | 00:00:02.13 | UPDATE counters SET n = n + 1 WHERE id = 1;
 8422 | app     | active              | LWLock          | buffer_mapping | 00:00:00.42 | SELECT * FROM counters WHERE id = 1;
 8510 | app     | active              | Lock            | transactionid  | 00:00:09.88 | UPDATE products SET stock = stock - 1 WHERE id = 7;

上例里前两行是存储层等待（LWLock），第三行是行锁等待（Lock），两者性质完全不同：前者等的是「别人正在改这一页」，后者等的是「别人持有这一行的事务锁」。

查看缓冲区中的热点页

想定位是哪个表、哪一页在被频繁争用，需要装 pg_buffercache 扩展：

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- 找出占用缓冲区最多（最热）的对象及其页数
SELECT c.relname,
       count(*)                                        AS buffers,
       pg_size_pretty(count(*) * current_setting('block_size')::int) AS cached_size,
       round(100.0 * count(*) / (SELECT count(*) FROM pg_buffercache), 1) AS pct
FROM pg_buffercache b
JOIN pg_class c ON c.oid = b.relfilenode
WHERE b.relfilenode <> 0
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

        relname        | buffers | cached_size | pct
-----------------------+---------+-------------+------
 counters              |      48 | 384 kB      | 22.4
 products              |      36 | 288 kB      | 16.8
 orders_pkey           |      21 | 168 kB      |  9.8

详细说明

页、缓冲区与缓冲区管理器

理解页级争用，必须先理解「页」在 PostgreSQL 里是怎么被访问的。磁盘 I/O 的单位是 页（Page / Block，固定 8 KB）；内存里缓存这些页的区域叫共享缓冲区（Shared Buffers），由缓冲区管理器（Buffer Manager）统一调度。

一次读取某一行的完整过程是：定位元组所在页 → 在缓冲区中查找该页 → 命中则读，未命中则从磁盘读入并占用一个缓冲区槽位。整个流程中，页级并发控制由 buffer_mapping 与 buffer_content 两把 LWLock 分别把守。

-- 查看缓冲区相关配置
SHOW shared_buffers;
SHOW block_size;
SHOW effective_cache_size;

 shared_buffers
----------------
 512MB
 block_size
------------
 8192

为什么「锁一页」会伤害并发

如果所有高并发更新都落在同一张表的同几页上，那么即使它们锁的是不同的行（不冲突），它们仍然会在 buffer_content 上排队——因为修改一页必须持有该页的排他内容锁（Exclusive Content Lock）。这就是「行不冲突但依然慢」的根本原因。

LWLock：轻量级锁

LWLock（Lightweight Lock，轻量级锁）是 PostgreSQL 内部保护共享内存数据结构的同步原语。它的特点是：

• 只有两种模式：共享（Shared） 与 排他（Exclusive），没有死锁检测（因此使用时必须严格遵守加锁顺序）。
• 持有时间极短，通常只有几十到几百纳秒。
• 不出现在 pg_locks 中——pg_locks 只展示重量级锁与谓词锁。
• 等待时可以通过 pg_stat_activity.wait_event_type = 'LWLock' 观察。

LWLock 按「tranche（分类）」组织，每个 tranche 有一个名字，这个名字就是等待时的 wait_event 值。

-- 按等待事件统计：当前有多少会话卡在各类 LWLock 上
SELECT wait_event,
       count(*)                                AS waiters,
       max(now() - state_change)               AS longest_wait
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
GROUP BY wait_event
ORDER BY waiters DESC;

   wait_event   | waiters | longest_wait
----------------+---------+--------------
 buffer_content |       7 | 00:00:01.204
 buffer_mapping |       2 | 00:00:00.318

buffer content lock 的共享与排他

缓冲区内容锁（Buffer Content Lock）是页级争用最核心的一把锁。规则很简单：

操作	加锁模式	说明
读取页中的行	共享（Shared）	多个读可以同时持有
修改页中的行（UPDATE / DELETE / INSERT / 加行锁）	排他（Exclusive）	同一时刻只能有一个

注意「给行加锁也要拿页的排他内容锁」这一点：因为 FOR UPDATE 要写元组的 t_xmax，属于「修改页」，所以必须拿排他内容锁。这意味着即使两行不冲突，只要在同一页上，加锁动作仍会短暂互斥——好在持有时间极短，正常不会成为瓶颈，除非页本身在磁盘 I/O 上很慢。

-- 观察：同页不同行的并发加锁（会短暂争用 buffer_content，但不会互相阻塞）
BEGIN;
SELECT * FROM counters WHERE id = 1 FOR UPDATE;   -- 页 A，行 1
-- 另一个会话：
-- SELECT * FROM counters WHERE id = 2 FOR UPDATE;  -- 页 A，行 2（不同行，不阻塞）
COMMIT;

-- 若两行恰好位于同一页，且页需要从磁盘读入，第二个会话可能观察到：
 wait_event_type | wait_event
-----------------+----------------
 LWLock          | buffer_content     ← 等待第一个事务写完 t_xmax

buffer_mapping 与 buffer_io 等待事件

除了 buffer_content，还有两把常见的 LWLock：

• buffer_mapping：保护 缓冲区映射表（Buffer Mapping Table）。任何「根据页号找缓冲区槽位」的动作都要拿它。它是分区（partitioned）的，所以通常不是瓶颈；一旦大量出现，往往意味着共享缓冲区太小、命中率低。
• buffer_io：标记「该缓冲区正在进行磁盘 I/O」。当某个后端要把脏页刷盘、或者要把页读入时，会持有它，其他想访问同一页的后端就会等待。它直接反映磁盘 I/O 慢。
• buffer_content：如上一小节，反映页内并发热点。

-- 一次查询里同时观察三类等待的分布
SELECT wait_event,
       count(*)                       AS sessions,
       round(avg(extract(epoch FROM now() - state_change)), 2) AS avg_wait_sec
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
  AND wait_event IN ('buffer_content', 'buffer_mapping', 'buffer_io')
GROUP BY wait_event;

   wait_event   | sessions | avg_wait_sec
----------------+----------+--------------
 buffer_io      |        5 |        12.40   ← 磁盘慢，页读不进来
 buffer_content |        3 |         0.08   ← 正常的页内互斥

判断口径

buffer_io 长期居高，说明瓶颈在存储（应检查磁盘、shared_buffers、是否被换页）；buffer_content 长期居高，说明瓶颈在并发写同一批页（应考虑 fillfactor、拆分热点、减少同页更新）。

页分裂：索引页的争用

需要特别澄清一个常见误解：PostgreSQL 的堆表（Heap Table）不存在页分裂——堆页只会插入、标记删除、以及被 VACUUM 整理，从不「分裂」。真正会发生页分裂（Page Split）的是 B-tree 索引页。

当向已满的索引页插入键值时，B-tree 会把它一分为二，并向上层传播。这个过程使用 Lehman-Yao 的并发算法，加锁单位是索引页：

插入类型	加锁方式	说明
普通插入（通常情况）	只有页级 LWLock，且很快释放	乐观路径，不阻塞读
会触发分裂的插入	短暂拿排他页锁，并向上传播	一般极快

因为 B-tree 用的是页级 LWLock 而不是重量级锁，所以索引插入不会造成长时间阻塞；但在单调递增主键（如 bigint 自增）场景下，所有插入都争抢最右侧页，可能形成 buffer_content 热点。

-- 观察索引页争用：高并发自增插入时，统计右侧页相关等待
SELECT wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock' AND wait_event LIKE 'buffer%'
GROUP BY wait_event;

   wait_event   | count
----------------+-------
 buffer_content |     6

为什么随机主键能缓解

单调递增主键让所有插入挤在 B-tree 最右侧页，形成热点；使用 UUID / 随机主键把插入分散到不同页，可以显著降低 buffer_content 争用——代价是索引扫描的局部性变差、缓存命中率下降。这是一个典型的权衡，没有普适答案。

热点更新、HOT 更新与 fillfactor

热点更新（Hot Update）指的是高并发下大量更新集中在少数几行/几页上（例如计数器、账户余额、库存）。这类更新会同时触发表级 ROW EXCLUSIVE 意向锁之外的行锁等待与页级 buffer_content 争用。

更麻烦的是版本膨胀：每次 UPDATE 都会写入新版本元组（New Tuple），旧版本要等 VACUUM 回收。如果新版本不能放进同一页，就要更新索引（因为指向该行的索引项必须改到新位置），成本立刻上升。

PostgreSQL 提供了 HOT（Heap-Only Tuple）更新优化：当更新没有修改任何被索引的列，且当前页还有足够空闲空间时，新版本直接放在同一页，旧行指针被改造成指向新版本的重定向链，从而完全跳过索引更新。

-- 默认 fillfactor=100，页会被填满，HOT 更新没有空间可用
CREATE TABLE counters (
  id  bigint PRIMARY KEY,
  n   bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 100);

-- 预留 30% 空间，让新版本有地方落，更易触发 HOT 更新
ALTER TABLE counters SET (fillfactor = 70);

-- 观察 HOT 更新命中率（n_tup_hot_upd 与 n_tup_upd 的比值）
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'counters';

 relname  | n_tup_upd | n_tup_hot_upd | hot_pct
----------+-----------+---------------+---------
 counters |    120000 |         98400 |    82.0

需要 VACUUM 才能真正回收

fillfactor 预留的空间会被不断产生的新版本填满；只有 VACUUM 回收了旧版本，页内空闲空间才会恢复，HOT 更新才能持续命中。因此热点表要保证自动清理（autovacuum）跟得上，必要时调低该表的 autovacuum_vacuum_scale_factor。

死锁的等待环与定位

死锁（Deadlock）的本质是等待环（Wait-for Cycle）：事务 A 等 B 持有的锁，事务 B 又在等 A 持有的锁，双方都永不释放。在 PostgreSQL 中，死锁既可能由行锁引起，也可能由表锁引起，但不会由 LWLock 引起——LWLock 没有死锁检测机制，所以内核代码必须靠严格的加锁顺序来避免。

手工还原等待环的利器是 pg_blocking_pids() 函数与 pg_locks：

-- 第一步：找出所有正在等待的会话，以及谁在阻塞它们
SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       wait_event_type,
       wait_event,
       left(query, 50) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

 pid  | blocked_by | wait_event_type |   wait_event   |          query
------+------------+-----------------+----------------+--------------------------
 9101 | {9102}     | Lock            | transactionid  | UPDATE accounts SET ... 
 9102 | {9101}     | Lock            | transactionid  | UPDATE accounts SET ...

上面输出就是教科书式的等待环：9101 等 9102，9102 又等 9101。这个环不会永远持续——死锁检测器会介入，中止其中一个。

-- 第二步：用 pg_locks 精确查看两个会话各自持有什么、在等什么
SELECT pid, locktype, relation::regclass AS rel, mode, granted, transactionid
FROM pg_locks
WHERE pid IN (9101, 9102)
ORDER BY pid, granted;

 pid  |   locktype    |  rel   |       mode       | granted | transactionid
------+---------------+--------+------------------+---------+---------------
 9101 | relation      | accounts | RowExclusiveLock | t       |
 9101 | transactionid |        | ExclusiveLock    | t       |         88001
 9101 | transactionid |        | ShareLock        | f       |         88002
 9102 | relation      | accounts | RowExclusiveLock | t       |
 9102 | transactionid |        | ExclusiveLock    | t       |         88002
 9102 | transactionid |        | ShareLock        | f       |         88001

读法：granted = t 是已持有，granted = f 是正在等待。9101 持有 88001、等 88002；9102 持有 88002、等 88001——环闭合。

deadlock_timeout 的触发机制

PostgreSQL 并不「实时」检测死锁，而是靠超时触发：当一个会话等待锁超过 deadlock_timeout（默认 1 秒） 仍未获得时，它才会去检查是否存在等待环。

SHOW deadlock_timeout;

-- 临时调整（需要超级用户或相应权限）
SET deadlock_timeout = '500ms';

 deadlock_timeout
------------------
 1s

这样设计的原因是性能：检测等待环需要获取锁管理器的内部锁并遍历等待图，代价不低；如果每次等待都检测，正常的短暂锁等待会付出大量无谓开销。默认 1 秒意味着任何超过 1 秒的等待都会触发一次检测，其中绝大多数会得出「无环」的结论，然后继续等待。

-- 让所有超过 1 秒的锁等待都写进日志，便于事后分析
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

-- 查看是否生效
SHOW log_lock_waits;

 log_lock_waits
----------------
 on

死锁与长锁等待的区别

两者都会表现为「SQL 卡住不动」，但性质与处理方式完全不同：

维度	死锁（Deadlock）	长锁等待（Lock Wait）
依赖关系	存在等待环，互相等	单向等待，等一个最终会结束的人
是否自动解除	是，检测器中止其中一个事务	否，必须等持有者提交/回滚，或人工干预
报错	40P01 deadlock detected	55P03（配 lock_timeout）或一直等待
触发开销	等待超过 deadlock_timeout 才检测	无额外开销
典型原因	更新顺序不一致、外键隐式锁、FOR UPDATE 顺序	长事务、idle in transaction、线上 DDL
处理手段	统一顺序、缩短事务（见「避免死锁」一节）	lock_timeout、statement_timeout、杀阻塞者

-- 区分现场：这个等待到底是「环」还是「单向长等待」
SELECT pid,
       pg_blocking_pids(pid)          AS blocked_by,
       wait_event_type,
       wait_event,
       now() - query_start            AS waited,
       left(query, 40)                AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

 pid  | blocked_by | wait_event_type |  wait_event   |   waited    |        query
------+------------+-----------------+---------------+-------------+----------------------
 9101 | {9102}     | Lock            | transactionid | 00:00:14.02 | UPDATE accounts ...
 9102 | {9101}     | Lock            | transactionid | 00:00:13.98 | UPDATE accounts ...
 9203 | {9204}     | Lock            | relation      | 00:01:52.31 | ALTER TABLE orders ...

前两行是环（死锁，但通常会在 1 秒内就被检测器处理掉，所以极少能看到它们同时长时间挂着）；第三行是单向长等待——9203 的 ALTER TABLE 在等 9204 释放表锁，只能等或超时。

观察技巧

死锁因为会被立刻打断，现场很难抓到；真正难处理的是单向长等待。所以实践中更常见的排查目标反而是「谁持有锁不放」——用 pg_blocking_pids() 反查阻塞者，再判断它是不是 idle in transaction。

实战示例

下面用一组完整的实验，从「看到页级争用」到「制造并定位死锁」走一遍。

第 1 步：建立热点表并观察元组头锁标记

DROP TABLE IF EXISTS hot_counter;

CREATE TABLE hot_counter (
  id  bigint PRIMARY KEY,
  n   bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 70);

INSERT INTO hot_counter (id, n)
SELECT g, 0 FROM generate_series(1, 1000) AS g;

ANALYZE hot_counter;

CREATE TABLE
INSERT 0 1000
ANALYZE

在会话 1 中开一个事务并加锁：

BEGIN;
SELECT * FROM hot_counter WHERE id = 1 FOR UPDATE;

 id | n
----+---
  1 | 0
(1 row)

第 2 步：验证「同页不同行不阻塞、同一行才阻塞」

在会话 2 中尝试锁同一页的另一行（应成功）：

SELECT * FROM hot_counter WHERE id = 2 FOR UPDATE;

 id | n
----+---
  2 | 0
(1 row)

再尝试锁会话 1 已经持有的行（应阻塞）：

SELECT * FROM hot_counter WHERE id = 1 FOR UPDATE;

-- 光标停住，等待 1 秒后触发一次死锁检测（此处无环，继续等待）
-- 用另一个会话观察：
SELECT wait_event_type, wait_event FROM pg_stat_activity WHERE pid = <会话2的pid>;

 wait_event_type |  wait_event
-----------------+--------------
 Lock            | transactionid

清理现场：

ROLLBACK;   -- 两个会话都执行

第 3 步：观察热点更新引发的 buffer_content 争用

准备一个压测脚本（在多个终端并行运行）：

-- 单行热点：所有会话更新同一行
DO $$
BEGIN
  FOR i IN 1..2000 LOOP
    UPDATE hot_counter SET n = n + 1 WHERE id = 1;
  END LOOP;
END $$;

同时在另一个会话持续采样等待事件：

SELECT count(*) FILTER (WHERE wait_event = 'buffer_content') AS content_waiters,
       count(*) FILTER (WHERE wait_event = 'transactionid')  AS rowlock_waiters
FROM pg_stat_activity
WHERE wait_event_type IN ('LWLock', 'Lock');

 content_waiters | rowlock_waiters
-----------------+-----------------
               4 |               9

结论：单行热点上，行锁等待（transactionid）是主因，页级争用（buffer_content）只是伴随现象。

第 4 步：用 fillfactor + HOT 观察膨胀改善

-- 对比两种 fillfactor 下的 HOT 更新比例
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_dead_tup,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'hot_counter';

   relname    | n_tup_upd | n_tup_hot_upd | n_dead_tup | hot_pct
--------------+-----------+---------------+------------+---------
 hot_counter  |      4000 |          3520 |        480 |    88.0

再手动清理，观察死元组下降：

VACUUM (VERBOSE, ANALYZE) hot_counter;

INFO:  vacuuming "public.hot_counter"
INFO:  "hot_counter": removed 480 dead row versions in 12 pages
VACUUM

第 5 步：制造一个真实的死锁

准备两张账户表，并让两个会话以相反顺序更新：

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  id      bigint PRIMARY KEY,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 1000), (2, 1000);

会话 1：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 停在这里，先不提交

会话 2：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 停在这里，先不提交

接着两会话交叉：

-- 会话 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 会话 2
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

约 1 秒（deadlock_timeout）后，其中一个会话报错：

ERROR:  deadlock detected
DETAIL:  Process 9102 waits for ShareLock on transaction 88001; blocked by process 9101.
         Process 9101 waits for ShareLock on transaction 88002; blocked by process 9102.
         See server log for query details.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,3) in relation "accounts"

另一个会话则成功执行，可以正常 COMMIT。注意 DETAIL 里已经把等待环完整打印出来了。

第 6 步：从日志还原等待环

如果现场已经过去，可以打开 log_lock_waits 后从日志里还原：

# postgresql.conf
log_lock_waits = on
log_min_duration_statement = 1000ms
deadlock_timeout = 1s

# 从日志中筛出死锁记录及其上下文
grep -n -A 12 "deadlock detected" /var/log/postgresql/postgresql-18-main.log | head -60

2026-09-28 10:12:31.442 CST [9102] ERROR:  deadlock detected
2026-09-28 10:12:31.442 CST [9102] DETAIL:  Process 9102 waits for ShareLock on transaction 88001; blocked by process 9101.
        Process 9101 waits for ShareLock on transaction 88002; blocked by process 9102.
        Process 9101: UPDATE accounts SET balance = balance + 100 WHERE id = 2;
        Process 9102: UPDATE accounts SET balance = balance + 100 WHERE id = 1;
2026-09-28 10:12:31.442 CST [9102] HINT:  See server log for query details.
2026-09-28 10:12:31.442 CST [9102] CONTEXT:  while updating tuple (0,3) in relation "accounts"

日志里同时给出了两个事务各自的完整 SQL——这比只看 pg_locks 更直观，直接暴露了「更新顺序不一致」这个根因。

注意事项

错误！不要以为 PostgreSQL 有间隙锁，从而用「锁范围」的思路做并发控制。

-- 错误！PostgreSQL 没有 gap lock，这条语句只锁住了 id=10 这一行，
-- 并不能阻止其他事务插入 id=11
BEGIN;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
COMMIT;

-- 正确做法：用 SERIALIZABLE 隔离级别 + 谓词锁，或显式锁一张「范围表」
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM orders WHERE id BETWEEN 1 AND 100;
INSERT INTO orders (id) VALUES (11);
COMMIT;

错误！把 pg_locks 当成能看所有锁的地方。

-- 错误认知：这里没看到等待，就以为没有锁争用
SELECT * FROM pg_locks WHERE NOT granted;
-- 事实：LWLock 与 BufferPin 根本不会出现在 pg_locks 里

-- 正确做法：存储层等待要去 pg_stat_activity 看
SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event_type IN ('LWLock', 'BufferPin');

错误！指望死锁检测器替你「预防」死锁。

死锁检测只是兜底：它会在 1 秒后中止一个事务并回滚。应用层如果直接把这个 40P01 报错抛给用户，用户体验就是「偶发失败」。正确做法是捕获并按幂等重试（详见 避免死锁的最佳实践）。

错误！在热点表上使用默认 fillfactor = 100。

-- 错误！页被填满，HOT 更新无处落脚，每次更新都会改索引
CREATE TABLE counters (id bigint PRIMARY KEY, n bigint) WITH (fillfactor = 100);

-- 正确：为频繁更新的表预留空间
CREATE TABLE counters (id bigint PRIMARY KEY, n bigint) WITH (fillfactor = 70);

不推荐：靠 pg_terminate_backend 粗暴解死锁。 死锁会被数据库自动处理；真正需要人工干预的是长锁等待，而干预前应当先用 pg_blocking_pids() 确认阻塞者是不是 idle in transaction，避免误杀正在正常工作的事务。

最佳实践

• 不要把「页级锁」当概念用：需要范围保护就用 SERIALIZABLE 或显式锁表，需要热点保护就拆分行/页。

-- 把一个热点计数器拆成 16 个槽位，降低单行争用
CREATE TABLE counters_sharded (
  id    integer PRIMARY KEY,   -- 槽位号 1..16
  n     bigint NOT NULL DEFAULT 0
);
-- 写入时随机选槽：UPDATE counters_sharded SET n = n + 1 WHERE id = (random()*15+1)::int;
-- 读取时求和： SELECT sum(n) FROM counters_sharded;

• 给频繁更新的宽表设置 fillfactor（如 70~90），让 HOT 更新命中率更高、减少索引膨胀。

ALTER TABLE orders SET (fillfactor = 80);

• 监控三类等待，区分瓶颈在锁还是在存储：

SELECT wait_event_type,
       wait_event,
       count(*) AS sessions
FROM pg_stat_activity
WHERE wait_event_type IN ('Lock', 'LWLock', 'BufferPin', 'IO')
GROUP BY 1, 2
ORDER BY 3 DESC;

• 及时发现长事务与 idle in transaction，它们是长锁等待的最大来源：

SELECT pid,
       state,
       now() - xact_start AS xact_age,
       left(query, 50)    AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '1 minute'
ORDER BY xact_start;

• 开启 log_lock_waits，让所有超过 deadlock_timeout 的等待留痕，事后可回溯。

log_lock_waits = on

• 不要用 LWLock 的思路去排查行锁，也不要用 pg_locks 去找 LWLock——两套机制的观测入口完全不同。

-- 行锁/表锁 → pg_locks + pg_blocking_pids
-- LWLock/BufferPin → pg_stat_activity.wait_event_type
SELECT 'heavyweight' AS kind, pid, locktype, mode, granted FROM pg_locks WHERE NOT granted
UNION ALL
SELECT 'lightweight', pid, wait_event_type, wait_event, false FROM pg_stat_activity WHERE wait_event_type = 'LWLock';

下一节

继续学习：锁等待与超时

参考链接

• PostgreSQL 官方文档：显式锁（Explicit Locking）
• PostgreSQL 官方文档：锁管理参数（runtime-config-locks）
• PostgreSQL 官方文档：pg_locks 视图
• PostgreSQL 官方文档：数据库页布局（Database Page Layout）
• PostgreSQL 官方文档：监控统计信息（Monitoring Database Activity）
• PostgreSQL 官方文档：系统信息函数（pg_blocking_pids）
• PostgreSQL 官方文档：VACUUM 与 fillfactor