PHP小码农

Appearance

行级锁（FOR UPDATE / FOR SHARE） ​

表级锁解决的是「DDL 与 DML 能不能并存」，而行级锁（Row-Level Lock）解决的是另一个更细的问题：「多个事务同时改同一批行时，谁先谁后」。一个电商系统在并发扣库存、抢券、对账时，能不能不出错，几乎完全取决于行级锁用得对不对。

本节把 PostgreSQL 行级锁的四种模式、它们背后的元组头实现、SELECT ... FOR UPDATE 的完整语法（OF / NOWAIT / SKIP LOCKED）、以及并发扣库存的三种经典方案一次讲透。

前置知识

阅读本节前，建议先了解：表级锁（ACCESS SHARE / EXCLUSIVE）、READ UNCOMMITTED / READ COMMITTED

基础概念 ​

行级锁的四种模式 ​

PostgreSQL 的行级锁只有四种，没有间隙锁（Gap Lock），也没有表级意向锁的独立概念（意向由 ROW SHARE / ROW EXCLUSIVE 表锁承担）：

模式英文语义自动加它的语句
FOR UPDATEUpdate Lock最强行锁，锁定后该行不可被更新/删除/加任何行锁SELECT ... FOR UPDATE
FOR NO KEY UPDATENo Key Update Lock类似 FOR UPDATE，但不阻止外键检查UPDATE（未改主键/唯一键时）
FOR SHAREShare Lock共享行锁，允许多个读锁共存，阻止更新SELECT ... FOR SHARE
FOR KEY SHAREKey Share Lock只锁「键值」，阻止主键/唯一键被改SELECT ... FOR KEY SHARE、外键检查

记忆方式：两把「排他」（FOR UPDATE / FOR NO KEY UPDATE）+ 两把「共享」（FOR SHARE / FOR KEY SHARE），后缀 KEY 的都表示「不碰键值」。

行锁冲突矩阵（4 × 4） ​

请求 \ 已持有FOR KEY SHAREFOR SHAREFOR NO KEY UPDATEFOR UPDATE
FOR KEY SHAREX
FOR SHAREXX
FOR NO KEY UPDATEXXX
FOR UPDATEXXXX

几个关键结论：

  • FOR KEY SHARE 之间完全兼容——多个外键检查可以同时进行。
  • FOR KEY SHARE 只与 FOR UPDATE 冲突——这就是「改主键会阻塞外键检查」的原因。
  • FOR SHARE 与 FOR NO KEY UPDATE 冲突——FOR SHARE 不允许任何人改这一行。
  • 三把锁都与 FOR UPDATE 冲突——FOR UPDATE 是最强的。

行锁标记在元组头上 ​

理解行锁的物理实现，才能理解为什么「行锁不占额外空间」以及「行锁看不到单独条目」。

PostgreSQL 的行锁不是一张独立的锁表，而是直接写在数据行的元组头（tuple header）里，具体用 xmax 字段：

text
┌──────────────────────────────────────────────────────┐
│  数据页（8 KB）                                        │
│  ┌──────────────────────────────────────────────┐    │
│  │ 行 1 元组头: xmin=100, xmax=205, infomask=... │    │  ← xmax=205 表示事务 205 锁住了它
│  │       数据: ...                                │    │
│  ├──────────────────────────────────────────────┤    │
│  │ 行 2 元组头: xmin=100, xmax=0,   infomask=... │    │  ← xmax=0 表示未被锁
│  │       数据: ...                                │    │
│  └──────────────────────────────────────────────┘    │
└──────────────────────────────────────────────────────┘
元组头字段作用
xmin创建此行的事务 ID
xmax删除/锁定此行的事务 ID（行锁就体现在这里）
infomask 中的 HEAP_XMAX_IS_MULTI / HEAP_XMAX_LOCK_ONLY区分「锁定」与「删除」

因此：

  • 行锁不占用锁表条目，这也是为什么「锁几百万行」不会耗尽内存。
  • 行锁在 pg_locks 里体现为 locktype='transactionid'，而不是一行一条记录。
  • locktype='tuple' 的锁很少对用户暴露，只有在等待一个「正在被锁定的元组」时才短暂出现。

行锁只在事务内有效 ​

行锁（以及所有锁）的生命周期都是到事务结束：

text
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 加行锁，持有中
-- ... 中间可以做任何事 ...
COMMIT;                                            -- 行锁在此刻释放

这意味着：

  • 自动提交模式下，SELECT ... FOR UPDATE 几乎没有意义——语句一结束事务就结束，锁立即释放。
  • 事务越长，行锁持有越久，冲突概率越高。
sql
-- 错误！自动提交模式下，锁在语句结束时立即释放
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

行锁与表锁的关系 ​

执行 SELECT ... FOR UPDATE 时，实际发生的是两级加锁：

text
1. 【表锁】在目标表上请求 ROW SHARE（不与 ROW EXCLUSIVE 冲突）
2. 【行锁】在满足条件的行的元组头 xmax 上打标记 FOR UPDATE

而 UPDATE / DELETE 是：

text
1. 【表锁】在目标表上请求 ROW EXCLUSIVE
2. 【行锁】在目标行的元组头上打 FOR NO KEY UPDATE（或 FOR UPDATE）标记

所以「写操作也会加行锁」——只是它自动帮你加了，不需要手写 FOR UPDATE。

语法与用法 ​

text
[ WITH ... ]
SELECT ... [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
                [ OF 表名 [, ...] ]
                [ NOWAIT | SKIP LOCKED ] ] ...

-- 四种模式
FOR UPDATE            -- 最强，阻止一切
FOR NO KEY UPDATE     -- 不阻止外键的 FOR KEY SHARE
FOR SHARE             -- 共享，阻止更新
FOR KEY SHARE         -- 仅锁键值，允许外键检查

-- 锁定范围限定：多表 JOIN 时只锁指定表
OF 表名

-- 冲突处理：NOWAIT 拿不到锁立即报错；SKIP LOCKED 跳过被锁的行不等待
NOWAIT | SKIP LOCKED

最简可运行示例（需要先 BEGIN，否则自动提交会让锁立即释放）：

sql
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
text
 id | owner | balance
----+-------+---------
  1 | alice | 1000.00
(1 row)

不加 FOR UPDATE 时，同一行可以被任意并发读（只加 ACCESS SHARE 表锁，无行锁）：

sql
BEGIN;
SELECT * FROM accounts WHERE id = 1;
text
 id | owner | balance
----+-------+---------
  1 | alice | 1000.00
(1 row)

详细说明 ​

四种模式逐个详解 ​

FOR UPDATE（最强）：锁定后，该行不能被其他事务 UPDATE / DELETE，也不能被加任何其他行锁。适合「读出来马上要改」的场景。

sql
BEGIN;
-- 锁定这一行，其他事务想改它会一直等待
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

FOR NO KEY UPDATE：与 FOR UPDATE 几乎一样强，唯一区别是不阻止 FOR KEY SHARE（即不阻止外键检查）。PostgreSQL 在普通 UPDATE 时默认加的就是它——因为「改一个非键列」不应该影响外键引用的有效性。

sql
-- 普通 UPDATE 自动加 FOR NO KEY UPDATE（未改主键/唯一键）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

FOR SHARE：共享行锁。多个事务可以同时持有同一行的 FOR SHARE，但任何人都不能更新这一行。适合「读出来后要基于它做别的写操作，但不改它本身」。

sql
BEGIN;
-- 允许其他事务也加 FOR SHARE，但阻止 UPDATE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

FOR KEY SHARE（最弱）：只锁「键值」（主键、唯一键）。它允许其他事务更新非键列，只阻止改键列。外键检查时自动加的就是它。

sql
-- 显式使用（很少手写）
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;

FOR KEY SHARE 为什么存在

外键检查的逻辑是「子表插入的 parent_id，父表里必须有对应行，且这一行在我检查期间不能被删/改主键」。FOR KEY SHARE 恰好只锁「键值不可变」这一点，允许父表行的其他列被并发更新——这是 PostgreSQL 让外键检查不至于阻塞一切的关键设计。

FOR UPDATE 与 FOR NO KEY UPDATE 的区别 ​

这是四种模式里最容易混淆的一对。差别只有一点：是否阻止外键检查。

text
子表 child(parent_id) REFERENCES parent(id)
                                  ▲
                                  │ 插入子行时，外键检查会对父表行加 FOR KEY SHARE
                                  │
父表 parent(id, name, score)
     ├── 事务 A 执行 FOR UPDATE        → 与 FOR KEY SHARE 冲突 → 子表插入被阻塞 ❌
     └── 事务 B 执行 FOR NO KEY UPDATE → 与 FOR KEY SHARE 兼容 → 子表插入正常 ✅

用实验证明：

sql
-- 准备父子表
CREATE TABLE parent (
  id   bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE child (
  id        bigint PRIMARY KEY,
  parent_id bigint NOT NULL REFERENCES parent(id)
);
INSERT INTO parent VALUES (1, 'p1');
sql
-- 会话 A：用 FOR UPDATE 锁父行
BEGIN;
SELECT * FROM parent WHERE id = 1 FOR UPDATE;
sql
-- 会话 B：插入子行需要对外键目标加 FOR KEY SHARE，被 A 挡住
INSERT INTO child VALUES (1, 1);
text
-- 一直等待，直到会话 A 提交

换成 FOR NO KEY UPDATE：

sql
-- 会话 A 改用 FOR NO KEY UPDATE
BEGIN;
SELECT * FROM parent WHERE id = 1 FOR NO KEY UPDATE;
sql
-- 会话 B：这次不会被阻塞（FOR NO KEY UPDATE 与 FOR KEY SHARE 兼容）
INSERT INTO child VALUES (2, 1);
text
INSERT 0 1

结论：业务里如果没有「防止主键被改」的强需求，优先用 FOR NO KEY UPDATE 而不是 FOR UPDATE，可以显著减少与外键检查的冲突。

UPDATE / DELETE 自动加行锁 ​

写语句会自动加行锁，且会阻塞其他事务对同一行的写操作：

sql
-- 会话 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
sql
-- 会话 B：同一行，会一直等待，直到会话 A COMMIT 或 ROLLBACK
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

但「读」不受影响（因为 MVCC 让读者看到旧版本）：

sql
-- 会话 C：普通查询不会被阻塞
SELECT * FROM accounts WHERE id = 1;
text
 id | owner | balance
----+-------+---------
  1 | alice | 1000.00     ← 看到的是 A 修改前的版本（MVCC 快照）
(1 row)

DELETE 同理，DELETE FROM accounts WHERE id = 2; 会给该行加行锁，其他事务改这行会等待。

值得注意的是：UPDATE 也会对满足条件但被阻塞的行产生影响。在 READ COMMITTED 下，如果 WHERE 条件命中的行被别的事务改了，PostgreSQL 会重新检查 WHERE（EvalPlanQual），这可能导致「看起来命中的行最后没被更新」。

行锁在 pg_locks 中的体现 ​

前面提到行锁写在元组头 xmax 上，但在 pg_locks 里，行锁主要体现为 locktype='transactionid'——即「等待某个事务 ID」：

sql
-- 会话 A：持有事务 12345 的行锁（实际上它是一个事务 ID 锁）
BEGIN;
UPDATE accounts SET balance = 1 WHERE id = 1;

-- 会话 B：等待事务 12345
BEGIN;
UPDATE accounts SET balance = 2 WHERE id = 1;

在会话 B 的等待期间查询：

sql
SELECT
  pid,
  locktype,
  mode,
  granted,
  transactionid,
  relation::regclass AS rel,
  page,
  tuple
FROM pg_locks
WHERE locktype IN ('transactionid', 'tuple')
ORDER BY granted DESC;
text
  pid  |    locktype    |     mode      | granted | transactionid |  rel   | page | tuple
-------+----------------+---------------+---------+---------------+--------+------+-------
 10231 | transactionid  | ExclusiveLock | t       |         12345 |        |      |
 10288 | transactionid  | ShareLock     | f       |         12345 |        |      |
(2 rows)

读法：

  • 持有 ExclusiveLock 的事务 ID 锁（pid 10231）= 它是「写者」。
  • 等待 ShareLock 的事务 ID 锁（pid 10288，granted=f）= 它在等写者释放。
  • transactionid 字段就是那个被锁定的事务 ID。

locktype='tuple' 才是直接指向「某个页某个元组」的锁（输出中带 relation / page / tuple 三列），但它只在特定场景短暂出现（比如等待一个正在被锁定的元组）。

不要依赖 tuple 锁做监控

locktype='tuple' 的出现是瞬时的、不确定的。排查行锁问题应该看 locktype='transactionid'，或者直接用 pg_blocking_pids()。

等待状态与 pg_blocking_pids ​

实际排查中，最直接的方式是 pg_stat_activity + pg_blocking_pids()：

sql
SELECT
  pid,
  state,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid) AS blockers,
  left(query, 60)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
text
  pid  | state  | wait_event_type | wait_event    | blockers |          query
-------+--------+-----------------+---------------+----------+-------------------------
 10288 | active | Lock            | transactionid | {10231}  | UPDATE accounts SET ba
(1 row)

注意 wait_event = 'transactionid'——这就是行锁等待的标志（对比表锁等待是 relation）。

进一步查阻塞者正在做什么：

sql
SELECT pid, state, xact_start, now() - xact_start AS age, left(query, 60) AS query
FROM pg_stat_activity
WHERE pid = 10231;
text
  pid  |        state        |          xact_start           |   age    |      query
-------+---------------------+-------------------------------+----------+------------------
 10231 | idle in transaction | 2026-09-28 10:00:01+08        | 00:05:33 | UPDATE accounts
(1 row)

state = 'idle in transaction' 是最危险的信号——它意味着事务开着但没在干活，锁却一直不放。

行锁等待与 lock_timeout ​

行锁等待会一直持续，除非：

  1. 持有者提交或回滚；
  2. 等待者设置了 lock_timeout（语句级锁等待上限）；
  3. 等待者设置了 statement_timeout（整条语句上限）；
  4. 被死锁检测中止（见 死锁检测机制）。
sql
-- 让等待最多 2 秒，超时立即失败
BEGIN;
SET LOCAL lock_timeout = '2s';
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
text
ERROR:  canceling statement due to lock timeout

SET LOCAL 的作用域是当前事务，事务结束自动恢复——这是给单个业务操作加超时的推荐方式（详见 锁等待与超时）。

也可以用 NOWAIT 完全不等待：

sql
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
text
ERROR:  could not obtain lock on row in relation "accounts"

行锁导致的死锁与「更新顺序」 ​

行锁是死锁最常见的来源。经典场景是两个事务以不同顺序更新同一批行：

text
时刻   会话 A                              会话 B
─────────────────────────────────────────────────────────
t1    BEGIN;
t2    UPDATE accounts SET ... WHERE id=1;  -- 锁住行 1
t3                                        BEGIN;
t4                                        UPDATE accounts SET ... WHERE id=2;  -- 锁住行 2
t5    UPDATE accounts SET ... WHERE id=2;  -- 等 B 释放行 2
t6                                        UPDATE accounts SET ... WHERE id=1;  -- 等 A 释放行 1
t7    ⛔ 死锁：A 等 B，B 等 A

PostgreSQL 的 deadlock_timeout（默认 1s）到期后触发检测，中止其中一个事务：

text
ERROR:  deadlock detected
DETAIL:  Process 10231 waits for ShareLock on transaction 12346; blocked by process 10288.
         Process 10288 waits for ShareLock on transaction 12345; blocked by process 10231.
HINT:  See server log for query details.

避免方法一句话：所有事务以相同的顺序访问行（详见 避免死锁的最佳实践）。

sql
-- 推荐：先排序再更新，保持全局一致的访问顺序
UPDATE accounts SET balance = balance - 100
WHERE id IN (SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id);

并发扣库存的三种经典方案 ​

这是一个几乎每家公司都会遇到的问题：多个请求同时买同一件商品，如何保证库存不超卖。

方案 A：悲观锁（FOR UPDATE）

sql
BEGIN;
-- 锁住这一行，其他并发者排队
SELECT stock FROM products WHERE id = 100 FOR UPDATE;
-- 应用层判断 stock > 0
UPDATE products SET stock = stock - 1 WHERE id = 100;
COMMIT;

方案 B：乐观锁（版本号 / CAS）

sql
-- 不带任何锁，直接条件更新（受影响行数为 0 即表示冲突）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 100 AND stock > 0 AND version = 5;

-- 更简洁：直接用 stock > 0 作为条件（原子递减）
UPDATE products SET stock = stock - 1 WHERE id = 100 AND stock > 0;

方案 C：SKIP LOCKED（队列化）

sql
BEGIN;
-- 跳过已被别人锁定的行，直接拿下一件；返回 0 行 = 无可用库存
SELECT id FROM inventory
WHERE product_id = 100 AND status = 'available'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE inventory SET status = 'reserved' WHERE id = ...;
COMMIT;

三者的优缺点对照：

方案优点缺点适用场景
A 悲观锁绝对准确、逻辑直观并发串行化、吞吐低需要「读-判断-写」多步且不能重试
B 乐观锁无锁等待、吞吐高冲突时需应用层重试库存是一个计数单元格（stock 字段）
C SKIP LOCKED多消费者零冲突需数据模型支持多行库存是多条独立记录（座位、券码）

SKIP LOCKED 的完整用法见 SKIP LOCKED 与 NOWAIT。

FOR UPDATE OF 与多表 JOIN ​

当一个查询 JOIN 多张表时，FOR UPDATE 默认会锁所有被 JOIN 的表。用 OF 限定只锁需要的表：

sql
-- 默认：锁 orders 和 users 两张表的相关行
SELECT o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.id = 100
FOR UPDATE;
sql
-- 推荐：只锁 orders（不想锁 users 表）
SELECT o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.id = 100
FOR UPDATE OF o;

OF 也可以指定多张表：

sql
SELECT o.id, i.qty
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.id = 100
FOR UPDATE OF o, i;

对视图加锁时需要小心：视图不能用 FOR UPDATE OF 限定基表名之外的名字（要写视图名），而且视图必须有可更新的基表。

用行锁实现「先读后写」的一致性 ​

FOR UPDATE 解决的核心问题是「读出来到写回去之间，值被别人改了」。看一个没有行锁的反例：

text
时刻   会话 A                                会话 B
─────────────────────────────────────────────────────────────
t1    SELECT balance → 1000
t2                                          SELECT balance → 1000
t3    UPDATE balance = 1000 - 100 = 900
t4                                          UPDATE balance = 1000 - 200 = 800
t5    COMMIT
t6                                          COMMIT
      -- 期望：1000 - 100 - 200 = 700
      -- 实际：800（A 的扣减被覆盖，丢失更新）

加上行锁后：

text
时刻   会话 A                                会话 B
─────────────────────────────────────────────────────────────
t1    BEGIN
t2    SELECT ... FOR UPDATE → 1000（持锁）
t3                                          BEGIN
t4                                          SELECT ... FOR UPDATE → 等待
t5    UPDATE balance = 900
t6    COMMIT                                 → 拿到锁，读到 900
t7                                          UPDATE balance = 900 - 200 = 700
t8                                          COMMIT
      -- 结果：700 ✅

对应的 SQL 就是「SELECT ... FOR UPDATE → 计算 → UPDATE → COMMIT」，这里不再重复展开。

在 READ COMMITTED 下，FOR UPDATE 还会带来一个额外保证：锁等待结束后，PostgreSQL 会重新读取该行的最新版本（EvalPlanQual），而不是返回等待前的旧快照。所以会话 B 拿到锁后会读到 900 而不是 1000。

实战示例 ​

场景：为电商系统实现安全的库存扣减，包含「防止超卖」「防止重复扣减」「避免长时间锁等待」三个要求。

第 1 步：建模并灌数据

sql
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS orders;

CREATE TABLE products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        text    NOT NULL UNIQUE,
  name       text    NOT NULL,
  stock      integer NOT NULL CHECK (stock >= 0),   -- 关键：负数直接被约束拦住
  version    integer NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES products(id),
  qty        integer NOT NULL CHECK (qty > 0),
  status     text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, stock) VALUES ('SKU-100', '限时秒杀商品', 100);
text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1

第 2 步：方案 A —— 悲观锁（FOR UPDATE）

sql
-- 会话 A：完整的一次下单扣减
BEGIN;
SET LOCAL lock_timeout = '3s';

-- 1) 锁定商品行
SELECT id, stock FROM products WHERE id = 1 FOR UPDATE;

-- 2) 应用层判断：stock >= qty 才继续
-- 3) 扣减库存
UPDATE products SET stock = stock - 5, updated_at = now() WHERE id = 1;

-- 4) 落订单
INSERT INTO orders (product_id, qty, status) VALUES (1, 5, 'paid');
COMMIT;
text
 id | stock
----+-------
  1 |   100
(1 row)

UPDATE 1
INSERT 0 1
COMMIT

同时，另一个会话会被挡住：

sql
-- 会话 B：在 A 未提交时执行，会一直等待
BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT id, stock FROM products WHERE id = 1 FOR UPDATE;
text
-- 排队等待，直到 A COMMIT
-- 拿到锁后读到 stock = 95

第 3 步：方案 B —— 乐观锁（条件更新，无锁等待）

sql
-- 一次原子递减 + 影响行数判断
UPDATE products
SET stock = stock - 3, version = version + 1, updated_at = now()
WHERE id = 1 AND stock >= 3;
text
UPDATE 1

如果库存不足，影响行数为 0——应用层读到 UPDATE 0 就知道「库存不足」，直接返回失败，全程无需等待、无需重试。这是最推荐的方案：

sql
-- 库存只剩 92，尝试扣 100 → 影响行数为 0
UPDATE products SET stock = stock - 100 WHERE id = 1 AND stock >= 100;
text
UPDATE 0

CHECK (stock >= 0) 约束是最后一道防线：即使逻辑写错，也只报错而不会出现负库存：

sql
-- 错误！约束会拦住超卖
UPDATE products SET stock = -1 WHERE id = 1;
text
ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (1, SKU-100, 限时秒杀商品, -1, 2, 2026-09-28 10:20:00+08).

第 4 步：观察行锁等待

sql
-- 会话 A：持锁不提交
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
sql
-- 会话 B：等待行锁（另开一个连接执行）
BEGIN;
SET LOCAL lock_timeout = '10s';
UPDATE products SET stock = stock - 1 WHERE id = 1;   -- 一直等待

在第三个会话里观察等待者与阻塞者：

sql
SELECT
  pid,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid) AS blockers,
  left(query, 50)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
text
  pid  | wait_event_type |  wait_event   | blockers |          query
-------+-----------------+---------------+----------+--------------------------
 10301 | Lock            | transactionid | {10299}  | UPDATE products SET stoc
(1 row)

wait_event = 'transactionid' 明确表示这是行锁等待，而非表锁等待（表锁是 relation）。

第 5 步：构造行锁死锁并观察

sql
-- 再插一个商品，制造两行
INSERT INTO products (sku, name, stock) VALUES ('SKU-200', '商品 B', 50);
text
INSERT 0 1
text
-- 会话 A                          -- 会话 B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;   -- 锁行 1
                                   BEGIN;
                                   UPDATE products SET stock = stock - 1 WHERE id = 2;   -- 锁行 2
UPDATE products SET stock = stock - 1 WHERE id = 2;   -- 等 B
                                   UPDATE products SET stock = stock - 1 WHERE id = 1;   -- 等 A → 死锁

1 秒后（deadlock_timeout 默认 1s）其中一个事务被中止：

text
ERROR:  deadlock detected
DETAIL:  Process 10299 waits for ShareLock on transaction 12350; blocked by process 10301.
         Process 10301 waits for ShareLock on transaction 12349; blocked by process 10299.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "products"

正确做法：统一顺序。 两个会话都按 id 升序加锁，就不会形成环：

sql
-- 推荐：用固定顺序的子查询先取锁，再更新
BEGIN;
WITH target AS (
  SELECT id FROM products WHERE id IN (1, 2) ORDER BY id FOR UPDATE
)
UPDATE products p
SET stock = p.stock - 1
FROM target t
WHERE p.id = t.id;
COMMIT;

第 6 步：清理

sql
DROP TABLE orders;
DROP TABLE products;

注意事项 ​

  • SELECT ... FOR UPDATE 在自动提交模式下毫无意义，锁会在语句结束瞬间释放。
sql
-- 错误！没有 BEGIN/COMMIT，锁不生效
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  • FOR UPDATE 是行锁里最强的一把，会阻塞外键检查。 能用 FOR NO KEY UPDATE 就不要用 FOR UPDATE。
sql
-- 不推荐：会阻塞子表插入
SELECT * FROM parent WHERE id = 1 FOR UPDATE;

  • 行锁持有到事务结束。 事务里不要做远程调用、等待用户输入、循环大查询；FOR UPDATE 也不会让 SELECT 读得更快，它只提供锁定语义，还会因为等待而变慢。

  • UPDATE ... WHERE 匹配很多行时会按扫描顺序加锁，不同执行计划可能产生不同加锁顺序，从而引发死锁。这是「批量更新容易死锁」的根因。

sql
-- 危险：大批量更新时加锁顺序不确定
UPDATE accounts SET level = 'vip' WHERE score > 1000;

  • lock_timeout 与 NOWAIT 是两套机制：前者超时抛 lock_not_available（55P03），后者一拿不到就抛错。二者都不能解决「死锁」，死锁由 deadlock_timeout 检测。

  • 不要用 FOR UPDATE 屏蔽并发问题后就不管了。它把并发变串行，高并发下会变成排队瓶颈。

  • 视图上的 FOR UPDATE 有额外限制，不能对含 DISTINCT / GROUP BY / UNION 的查询加行锁。

sql
-- 错误！含聚合的查询无法加行锁
SELECT user_id, count(*) FROM orders GROUP BY user_id FOR UPDATE;
text
ERROR:  FOR UPDATE is not allowed with GROUP BY clause
  • FOR KEY SHARE 不能阻止非键列被改，别用它来保护业务字段。pg_locks 里行锁体现在 transactionid，tuple 锁一般不对用户暴露，不要依赖它做监控。

最佳实践 ​

  • 优先用「原子条件更新」（乐观锁）而不是 FOR UPDATE，它能避免锁等待，吞吐更高。
sql
UPDATE products SET stock = stock - 1 WHERE id = 100 AND stock > 0;
-- 检查影响行数即可，无需重试逻辑
  • 必须用悲观锁时，选 FOR NO KEY UPDATE，避免阻塞外键检查。
sql
SELECT * FROM orders WHERE id = 100 FOR NO KEY UPDATE;
  • 给每个带行锁的业务语句配 lock_timeout，让冲突快速失败。
sql
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
COMMIT;
  • 批量更新前先把目标行排序，保证全局一致的加锁顺序。
sql
WITH target AS (
  SELECT id FROM accounts WHERE score > 1000 ORDER BY id FOR UPDATE
)
UPDATE accounts a SET level = 'vip' FROM target t WHERE a.id = t.id;
  • 用 CHECK 约束兜底，把「负库存」这类业务不变量交给数据库强制。
sql
stock integer NOT NULL CHECK (stock >= 0)

  • 保持事务短小，锁持有时间与事务时长成正比。

  • 需要保护多行等价资源时用 SKIP LOCKED，而不是让消费者互相等待。

sql
SELECT id FROM inventory
WHERE product_id = 100 AND status = 'available'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;
  • 监控长事务与行锁等待，把 idle in transaction 当告警处理：
sql
SELECT pid, state, now() - xact_start AS age, left(query, 50) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '1 minute';
  • 给应用层的「读-改-写」流程加版本号，即使不用数据库锁也能检测并发冲突；同时不要在外键列上执行长事务的 FOR UPDATE，外键检查与行锁的交互很容易造成意外阻塞。
sql
ALTER TABLE products ADD COLUMN version integer NOT NULL DEFAULT 0;
-- 更新时带上 version 条件，受影响行数为 0 即表示冲突

下一节

继续学习：页级锁与死锁

参考链接 ​