PostgreSQL / 系统课时
使用 SELECT FOR UPDATE
使用 SELECT FOR UPDATE
锁定读取的目的不是“让 SELECT 更安全”,而是在读取业务对象时提前声明后续并发约束。这节课用同一行账户记录验证阻塞,再用冲突矩阵区分 FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE 与 FOR KEY SHARE 的强弱和适用范围。
学习目标
- 用
SELECT ... FOR UPDATE锁住已读取行并验证并发写等待。 - 根据冲突矩阵正确选择四种行级锁模式。
- 使用
OF、NOWAIT、SKIP LOCKED控制锁定对象和等待策略。
锁定读取解决什么问题
普通 SELECT 不会阻止其他事务修改它读到的行。SELECT ... FOR UPDATE 则会对返回的行取得行级锁,并一直持有到当前事务结束;同一行上的冲突写操作或更弱/更强的冲突锁定读取需要等待。锁定读取还会在目标表上取得 RowShareLock。:contentReference[oaicite:3]{index=3}
先创建一张独立实验表。
DROP TABLE IF EXISTS account_lock;
CREATE TABLE account_lock (
id bigint PRIMARY KEY,
balance integer NOT NULL,
note text NOT NULL
);
INSERT INTO account_lock (id, balance, note)
VALUES (1, 100, 'ready');预期输出
DROP TABLE
CREATE TABLE
INSERT 0 1用 FOR UPDATE 验证阻塞
会话 A 开启事务并锁定 id = 1。注意,单纯执行这条 SELECT 就已经把该行锁住,不必先做 UPDATE。
BEGIN;
SELECT id, balance, note
FROM account_lock
WHERE id = 1
FOR UPDATE;预期输出
BEGIN
id | balance | note
----+---------+-------
1 | 100 | ready保持会话 A 的事务打开,在会话 B 设置 2 秒锁等待上限,然后尝试更新同一行。
SET lock_timeout = '2s';
UPDATE account_lock
SET balance = balance - 10
WHERE id = 1;预期结果
SET
ERROR: canceling statement due to lock timeout回到会话 A 结束事务,锁随事务释放。
ROLLBACK;预期输出
ROLLBACK这个实验说明:行级锁不会阻止普通查询读取同一行,但会阻止与它冲突的写入或锁定读取。行锁的生命周期跟随事务,而不是跟随单条 SELECT。:contentReference[oaicite:4]{index=4}
比较四种行级锁模式
四种模式可以按“允许别人同时做什么”来理解。下表中的 X 表示同一行已经持有列对应锁时,新请求行对应锁会发生冲突。:contentReference[oaicite:5]{index=5}
| 新请求 \ 已持有 | FOR KEY SHARE | FOR SHARE | FOR NO KEY UPDATE | FOR UPDATE |
|---|---|---|---|---|
| FOR KEY SHARE | 允许 | 允许 | 允许 | X |
| FOR SHARE | 允许 | 允许 | X | X |
| FOR NO KEY UPDATE | 允许 | X | X | X |
| FOR UPDATE | X | X | X | X |
FOR UPDATE 最强,适合读取后明确要修改或删除该行、且不希望任何其他锁定读取并行推进的场景。DELETE 会自动取得这种强度的行锁;UPDATE 修改了可用于外键引用的唯一键列时,也会使用这种更强的锁。:contentReference[oaicite:6]{index=6}
FOR NO KEY UPDATE 比 FOR UPDATE 弱,它允许其他事务同时取得 FOR KEY SHARE。PostgreSQL 对不需要 FOR UPDATE 强度的普通 UPDATE 会自动使用这一模式,因此更新非键业务字段时,它能减少对引用关系检查的阻塞。:contentReference[oaicite:7]{index=7}
FOR SHARE 允许其他事务继续取得 FOR SHARE 或 FOR KEY SHARE,但会阻止 UPDATE、DELETE、FOR UPDATE 和 FOR NO KEY UPDATE。FOR KEY SHARE 最弱,主要保护键不被删除或改动,同时允许不会改变键值的更新继续进行。:contentReference[oaicite:8]{index=8}
控制锁定范围与等待方式
在连接查询里,锁定子句默认可能作用于查询涉及的可锁定目标;需要只锁某张表时,用 OF table_name 明确限定。NOWAIT 让语句遇到冲突立即报错,适合请求链路不允许排队的场景;SKIP LOCKED 会跳过已被其他事务锁住的行,适合多个 worker 竞争任务队列,但它得到的是不一致视图,不应当当作普通业务查询的替代品。:contentReference[oaicite:9]{index=9}
下面演示队列消费者常用的“只取一个当前可锁定任务”。这是完整 SQL,但要观察“跳过”效果,需要另一会话先锁住某条 ready 记录。
SELECT id, balance, note
FROM account_lock
WHERE note = 'ready'
ORDER BY id
LIMIT 1
FOR UPDATE OF account_lock SKIP LOCKED;在没有其他会话持锁时,预期输出为当前唯一记录。
id | balance | note
----+---------+-------
1 | 100 | ready如果 id = 1 已被另一事务以冲突模式锁住,同一查询的预期结果是 0 行,而不是等待。
id | balance | note
----+---------+-----
(0 rows)选择锁模式时,优先使用能满足业务不变量的最弱模式:越强的锁越容易扩大等待链。需要“拿到这行后一定修改或删除”时用 FOR UPDATE;只改非键字段可考虑 FOR NO KEY UPDATE;只需共享保护时再考虑 FOR SHARE 或 FOR KEY SHARE。真正需要避免等待时,再明确选择 NOWAIT 或 SKIP LOCKED,不要靠无限等待来维持正确性。