PHP小码农

Appearance

行锁查询（FOR UPDATE） ​

FOR UPDATE 等锁定子句让 SELECT 在读取行的同时对其加行级锁，用于"读—改—写"场景中防止并发丢失更新。本节讲解四种锁定模式的差异、NOWAIT 与 SKIP LOCKED、队列消费模式实战，以及行锁与事务边界、死锁风险的关系。

前置知识

阅读本节前，建议先了解：分组（GROUP BY / HAVING）

基础概念 ​

术语对照 ​

中文English说明
行级锁Row-Level Lock锁作用于具体的行
表级锁Table-Level Lock锁作用于整张表
锁定子句Locking ClauseFOR UPDATE 等附加在 SELECT 上
事务Transaction锁在事务提交或回滚时释放
阻塞Blocking后到的锁请求等待先到的持锁者
死锁Deadlock两事务互相等待对方持有的锁
队列消费Queue Consumption多消费者抢占任务并标记为已处理
丢失更新Lost Update后写覆盖先写，更新丢失

为什么需要行锁 ​

考虑一个"扣库存"场景，两个事务并发执行：

sql
-- 事务 A 与事务 B 交错执行（无锁保护）
-- A: 读取 stock = 10
-- B: 读取 stock = 10
-- A: 写入 stock = 9  （扣减 1）
-- B: 写入 stock = 9  （扣减 1）
-- 结果：只扣了 1，实际应扣 2 —— 丢失更新

问题的根源是"读取"与"写入"之间无法保证行未被他人修改。FOR UPDATE 在读取时立即加锁，让后续 UPDATE 有的放矢：

sql
BEGIN;
-- 读取并锁住该行；若已被其他事务锁定，此处会阻塞等待
SELECT stock FROM products WHERE sku = 'SKU-1001' FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE sku = 'SKU-1001';
COMMIT;   -- 锁在提交时释放

锁的持有时间 ​

FOR UPDATE 加的锁一直持有到当前事务结束（COMMIT 或 ROLLBACK），与语句是否执行完毕无关。因此：

  • 必须把 SELECT ... FOR UPDATE 及后续修改放在同一个显式事务中；
  • 事务越短越好，避免长时间持锁导致其他会话阻塞；
  • 若在 BEGIN 之外执行，FOR UPDATE 的锁会立即随隐式事务提交而释放，等于没锁。

语法与用法 ​

完整语法 ​

sql
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
[FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
  [OF table_name [, ...]]
  [NOWAIT | SKIP LOCKED]];
子句作用
FOR UPDATE最强行锁，阻止他人 UPDATE / DELETE / 所有锁定子句
FOR NO KEY UPDATE较弱，允许并发的 FOR KEY SHARE（如外键检查）
FOR SHARE共享锁，阻止他人 UPDATE / DELETE，允许他人加共享锁
FOR KEY SHARE最弱，只阻止 DELETE 与键更新（外键引用的典型加锁）
OF table_name只锁定指定表（多表连接时用）
NOWAIT不等待，遇到已锁定的行立即报错
SKIP LOCKED跳过已锁定的行，只锁定空闲行

示例数据 ​

后续示例使用 demo 库中的 jobs 表（任务队列）：

sql
CREATE TABLE jobs (
  id          bigserial   PRIMARY KEY,
  job_type    varchar(30) NOT NULL,
  payload     jsonb       NOT NULL,
  status      varchar(10) NOT NULL DEFAULT 'queued',  -- queued/running/done/failed
  worker      varchar(30),
  attempts    integer     NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_jobs_status_id ON jobs (status, id);

INSERT INTO jobs (id, job_type, payload, status) VALUES
  (1, 'send_email',  '{"to":"a@x.com"}',   'queued'),
  (2, 'send_email',  '{"to":"b@x.com"}',   'queued'),
  (3, 'sync_order',  '{"order":"SO-1001"}','running'),
  (4, 'sync_order',  '{"order":"SO-1002"}','queued'),
  (5, 'gen_report',  '{"month":"2026-01"}','done');
text
 id |  job_type  |        payload        | status  | worker | attempts
----+------------+-----------------------+---------+--------+----------
  1 | send_email | {"to": "a@x.com"}     | queued  |        |        0
  2 | send_email | {"to": "b@x.com"}     | queued  |        |        0
  3 | sync_order | {"order": "SO-1001"}  | running |        |        0
  4 | sync_order | {"order": "SO-1002"}  | queued  |        |        0
  5 | gen_report | {"month": "2026-01"}  | done    |        |        0
(5 rows)

详细说明 ​

四种锁定模式对比 ​

模式与 FOR KEY SHARE与 FOR SHARE与 FOR NO KEY UPDATE与 FOR UPDATE
FOR KEY SHARE兼容兼容兼容冲突
FOR SHARE兼容兼容冲突冲突
FOR NO KEY UPDATE兼容冲突冲突冲突
FOR UPDATE冲突冲突冲突冲突

按锁强度从弱到强：FOR KEY SHARE < FOR SHARE < FOR NO KEY UPDATE < FOR UPDATE。

选择原则：

场景推荐模式理由
读后要 UPDATE 非键列FOR NO KEY UPDATE不阻塞外键检查，并发度更高
读后要 UPDATE 键列或 DELETEFOR UPDATE需阻止键被引用/修改
只读但要防止被改FOR SHARE阻止写，允许其他读者
外键引用校验FOR KEY SHAREPostgreSQL 内部即用此模式
sql
-- 只需修改非键列（如 status），用较弱的锁即可
BEGIN;
SELECT id FROM jobs WHERE id = 1 FOR NO KEY UPDATE;
UPDATE jobs SET status = 'running' WHERE id = 1;
COMMIT;

FOR UPDATE 的典型用法：读后写 ​

sql
-- 场景一：账户余额扣减，必须先锁定再校验
BEGIN;

SELECT balance FROM accounts WHERE user_id = 1001 FOR UPDATE;
-- 应用层判断：余额是否充足？

UPDATE accounts
SET balance = balance - 500.00
WHERE user_id = 1001;

COMMIT;
text
 balance
---------
 3000.00
(1 row)

UPDATE 1
COMMIT

关键点：FOR UPDATE 保证从 SELECT 到 UPDATE 之间没有其他事务能修改这一行，从而消除丢失更新。

NOWAIT：不等待，立即失败 ​

默认情况下，若目标行已被其他事务锁定，FOR UPDATE 会一直阻塞等待。NOWAIT 改为立即报错：

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

适用于"宁可失败也不要排队"的场景，例如用户界面上的即时操作，避免请求堆积。

SKIP LOCKED：跳过已锁定行 ​

SKIP LOCKED 只返回并锁定未被锁定的行，跳过被其他事务占用的行。这是实现高并发任务队列的核心手段：

sql
BEGIN;
-- 抢占一个待处理任务，跳过其他 worker 已锁定的行
SELECT id, job_type, payload
FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
text
 id |  job_type  |      payload
----+------------+--------------------
  1 | send_email | {"to": "a@x.com"}
(1 row)

若此时另一个会话执行相同语句，它会跳过 id = 1，拿到 id = 2，而不是阻塞等待。这正是队列"多消费者并行"所需要的语义。

选项遇到已锁定行适用场景
（默认）阻塞等待必须拿到该行的短事务
NOWAIT立即报错交互式操作、怕排队
SKIP LOCKED跳过并继续任务队列、批量分发

OF：多表连接时指定加锁目标 ​

在多表连接中，FOR UPDATE 默认会锁定所有表中被引用的行。若只想锁某张表，用 OF 指定：

sql
-- 只锁定 jobs 表，不锁 users 表
SELECT j.id, j.job_type, u.user_name
FROM jobs j
JOIN users u ON u.user_name = j.payload->>'owner'
WHERE j.status = 'queued'
FOR UPDATE OF j SKIP LOCKED
LIMIT 5;

OF 后可以列出多个表名。注意：OF 只能指定 FROM 中出现的表（或其别名）。

锁定与 LIMIT / ORDER BY 的顺序 ​

FOR UPDATE 会在行被返回后加锁，因此与 LIMIT、ORDER BY 的组合有一个重要陷阱：若排序键取值相同，被锁定与返回的行可能具有随机性，导致多个消费者锁定同一批行中的不同子集。

正确做法是让 ORDER BY 使用唯一键（如 id），或使用 ORDER BY ... , id：

sql
-- 推荐：排序键唯一，锁定确定
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

行锁与事务边界 ​

sql
-- 错误！无显式事务，锁随语句结束立即释放，形同虚设
SELECT * FROM jobs WHERE id = 1 FOR UPDATE;
UPDATE jobs SET status = 'running' WHERE id = 1;

-- 正确：显式事务包裹读与写
BEGIN;
SELECT * FROM jobs WHERE id = 1 FOR UPDATE;
UPDATE jobs SET status = 'running' WHERE id = 1;
COMMIT;

锁必须在事务内才有意义。同时，事务应尽量短：

事务时长影响
毫秒级锁竞争低，吞吐高
秒级其他会话开始排队，响应变慢
分钟级长事务阻塞，可能触发连接池耗尽
含外部 I/O极危险，锁被长期占用，且影响 autovacuum 回收

死锁风险 ​

两个事务以不同顺序锁定同一批行时，会互相等待形成死锁：

sql
-- 会话 A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 锁住 1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;   -- 等待 B 释放 2

-- 会话 B（并发）
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;   -- 锁住 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 等待 A 释放 1
text
ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 678; blocked by process 12346.
HINT:  See server log for query details.

PostgreSQL 会在检测到死锁后主动终止其中一个事务（deadlock_timeout 默认 1s）。防范手段：

手段说明
统一加锁顺序所有事务都按 id 升序加锁
用单条语句加锁SELECT ... WHERE id IN (1,2) ORDER BY id FOR UPDATE 一次拿全
缩短事务减少锁持有时间与交叉窗口
用 SKIP LOCKED避免排队等待
设置 lock_timeout超时主动放弃，避免无限等待
sql
-- 一次性按固定顺序锁定多行，避免交叉加锁
BEGIN;
SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
COMMIT;

隔离级别配合 ​

FOR UPDATE 的行为与事务隔离级别有关：

隔离级别与 FOR UPDATE 的关系
READ COMMITTED（默认）若目标行已被修改，等待提交后重新读取最新版本再判断条件
REPEATABLE READ等待提交后，若版本已变则报 could not serialize access due to concurrent update
SERIALIZABLE在 REPEATABLE READ 基础上做谓词锁检查，可能报序列化失败
sql
-- 显式设置隔离级别（默认为 read committed）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
COMMIT;

在 READ COMMITTED 下，FOR UPDATE 会"重新评估" WHERE 条件（EvalPlanQual 机制），可能导致原先匹配的行在等待后不再匹配而被跳过，需要应用层注意返回值。

实战示例 ​

需求：实现一个可靠的多消费者任务队列，支持并发抢占、失败重试、超时回收。步骤 1~7。

步骤 1：单消费者抢占一个任务 ​

sql
BEGIN;

UPDATE jobs
SET status = 'running',
    worker = 'worker-1',
    attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING id, job_type, payload;

COMMIT;
text
 id |  job_type  |      payload
----+------------+--------------------
  1 | send_email | {"to": "a@x.com"}

UPDATE 1
COMMIT

这是队列消费的标准写法：用 SKIP LOCKED 抢占未被锁定的任务，在同一事务内直接标记为 running，避免"先查后改"的竞态。

步骤 2：验证多消费者不会拿到同一任务 ​

sql
-- 会话 A：开事务并持锁不提交
BEGIN;
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
text
 id
----
  1
(1 row)
sql
-- 会话 B：并发执行同一语句，跳过 A 已锁定的 id=1
BEGIN;
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
text
 id
----
  2
(1 row)

会话 B 未阻塞，直接拿到 id = 2。这就是 SKIP LOCKED 的价值。

步骤 3：对比 NOWAIT 的行为 ​

sql
-- 会话 B（A 仍持有 id=1 的锁）
BEGIN;
SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;
text
ERROR:  could not obtain lock on row in relation "jobs"

NOWAIT 不等待而是直接失败，适合"快速失败"的交互场景。

步骤 4：批量抢占多个任务 ​

sql
BEGIN;

UPDATE jobs
SET status = 'running', worker = 'worker-2', attempts = attempts + 1
WHERE id IN (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
RETURNING id, job_type;

COMMIT;
text
 id |  job_type
----+------------
  2 | send_email
  4 | sync_order

UPDATE 2
COMMIT

一次抢占一批，减少事务往返次数，是高吞吐队列的常用优化。

步骤 5：任务完成与失败回写 ​

sql
-- 成功完成
BEGIN;
UPDATE jobs
SET status = 'done'
WHERE id = 1 AND worker = 'worker-1' AND status = 'running';
COMMIT;

-- 失败且未超重试上限，退回队列
BEGIN;
UPDATE jobs
SET status = CASE WHEN attempts >= 3 THEN 'failed' ELSE 'queued' END,
    worker = NULL
WHERE id = 2 AND worker = 'worker-1' AND status = 'running';
COMMIT;

用 worker 与 status 作为 WHERE 条件（乐观并发保护），防止已超时被他人接管的旧 worker 覆盖结果。

步骤 6：超时任务回收 ​

sql
-- 将运行超过 10 分钟的任务重新置为 queued
BEGIN;
SELECT id FROM jobs
WHERE status = 'running'
  AND created_at < now() - interval '10 minutes'
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'queued', worker = NULL
WHERE id IN (
  SELECT id FROM jobs
  WHERE status = 'running' AND created_at < now() - interval '10 minutes'
)
RETURNING id;
COMMIT;

步骤 7：查看当前锁与等待情况 ​

sql
-- 当前行锁持有情况
SELECT locktype,
       relation::regclass AS table_name,
       transactionid,
       mode,
       granted,
       pid
FROM pg_locks
WHERE locktype IN ('transactionid', 'tuple')
ORDER BY granted, pid;
text
    locktype    | table_name | transactionid |     mode      | granted |  pid
----------------+------------+---------------+---------------+---------+-------
 transactionid  |            |         74123 | ExclusiveLock | t       | 12345
 transactionid  |            |         74124 | ShareLock     | f       | 12346
(2 rows)

granted = f 表示该锁请求正在等待。配合 pg_stat_activity 可定位是谁在阻塞：

sql
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY a.pid;
text
  pid  |        state        | wait_event_type | wait_event |          query
-------+---------------------+-----------------+------------+--------------------------
 12346 | active              | Lock            | transactionid | SELECT id FROM jobs W...
(1 row)

注意事项 ​

  1. FOR UPDATE 必须在显式事务中使用。 无 BEGIN 时锁随语句结束即释放，起不到保护作用。

  2. 锁持有到事务结束，不是语句结束。 长事务会长期阻塞他人，也会阻碍 vacuum 回收死元组。

  3. 不要在持锁事务中做外部 I/O。 调用 HTTP 接口、发送消息等耗时操作会让锁被占用数秒甚至数分钟。

  4. FOR UPDATE 与 LIMIT 组合要看排序键是否唯一，否则锁定的行不确定。

sql
-- 不推荐：status 有大量重复值，排序不稳定
SELECT id FROM jobs WHERE status = 'queued' ORDER BY status FOR UPDATE SKIP LOCKED LIMIT 1;

-- 推荐：用唯一键排序
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
  1. FOR UPDATE 不能用于聚合、GROUP BY、DISTINCT、窗口函数的结果上。 因为加锁对象必须是基表的原始行。
sql
-- 错误！锁定子句不能与聚合/分组同时使用
SELECT city, count(*) FROM orders GROUP BY city FOR UPDATE;

  1. SKIP LOCKED 会返回不一致的快照。 它有意跳过部分行，若用在不希望漏数据的场景（如对账），会造成误判。

  2. NOWAIT 与 SKIP LOCKED 不能同时使用。

  3. 死锁无法完全避免，只能降低概率。 应用层需能识别 deadlock detected 并重试。

最佳实践 ​

  1. 事务尽量短，锁范围尽量小。 先算好数据、只锁必要行与必要表。
sql
BEGIN;
SELECT stock FROM products WHERE sku = 'SKU-1001' FOR NO KEY UPDATE;
UPDATE products SET stock = stock - 1 WHERE sku = 'SKU-1001';
COMMIT;
  1. 统一加锁顺序，从源头规避死锁。 批量加锁时用 ORDER BY id 固定顺序。
sql
SELECT * FROM accounts WHERE id IN (1, 2, 3) ORDER BY id FOR UPDATE;

  1. 能改非键列就用 FOR NO KEY UPDATE，它不阻塞外键的 FOR KEY SHARE 检查，并发度更高。

  2. 队列消费一律用 FOR UPDATE SKIP LOCKED + 同一事务内改状态。

sql
UPDATE jobs
SET status = 'running', worker = $1, attempts = attempts + 1
WHERE id = (SELECT id FROM jobs WHERE status = 'queued'
            ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING *;
  1. 设置 lock_timeout 与 idle_in_transaction_session_timeout，让失控事务自动释放。
sql
-- 会话级：等待锁最多 3 秒
SET lock_timeout = '3s';

-- 会话级：事务空闲超过 60 秒自动终止
SET idle_in_transaction_session_timeout = '60s';
  1. 为队列的筛选与排序建索引，避免每次抢占都全表扫描。
sql
-- 支撑 WHERE status = 'queued' ORDER BY id
CREATE INDEX idx_jobs_status_id ON jobs (status, id);
  1. 对 deadlock detected 与序列化失败做重试，不要在应用层把它当致命错误。
sql
-- 应用层伪代码：捕获 SQLSTATE 40P01（死锁）后重试 1~3 次
-- BEGIN;
--   SELECT ... FOR UPDATE;
-- COMMIT;
-- on SQLSTATE 40P01: retry
  1. 用 pg_locks + pg_stat_activity 定期巡检锁等待，把慢事务揪出来。
sql
SELECT a.pid, a.wait_event, now() - a.xact_start AS xact_age, left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock' OR a.state = 'idle in transaction'
ORDER BY xact_age DESC;

下一节

继续学习：算术与比较运算符

参考链接 ​