企业官网建设流程全解析

上周二凌晨两点，某平台的批处理任务又一次卡死在线上了。看日志，任务停在那条连了三张表的UPDATE语句上，等待事件是lock: tuple。我第一反应是“又被谁锁了”，拉出pg_stat_activity一查，真正阻塞者是一个已经开了两小时事务的后台查询。这个查询本身只是做联表统计，但语句带上了FOR UPDATE，把一批订单行锁得死死的，批处理等不到锁，整条任务线跟着瘫痪。

类似的问题我已经遇到过不止一次。很多人写 PostgreSQL 联表查询时，默认“查询就是读，读不会锁数据”，等到某天发现查询卡住、结果变少、甚至死锁，才意识到问题没那么简单。这篇文章就把我和“联表加锁”纠缠这些年的经验一次讲清楚，从锁的基本模型到三个最阴的陷阱，再到排查实操，能帮大家少走不少弯路。

1. 先看清问题：联表查询到底在什么时候动了锁

1.1 哪些行为看似“只读”却要加锁

普通SELECT在 PostgreSQL 的 MVCC 机制下确实不加行锁，查询走的是快照，写操作不阻塞读。但这句“不加锁”只适用于最朴素的查询。一旦语句里出现下面这些东西，锁就悄悄出现了：

• SELECT ... FOR UPDATE或SELECT ... FOR SHARE，包括它们的变化形式FOR NO KEY UPDATE、FOR KEY SHARE；
• UPDATE ... FROM ...这种在 PostgreSQL 里用连表方式更新数据的写法；
• DELETE ... USING ...连表删除的写法；
• 对外键约束的完整性检查，尤其在更新、删除被引用表（父表）数据时，会自动去引用表（子表）上确认是否存在引用行；
• 某些在READ COMMITTED隔离级别下的并发写入，配合ON CONFLICT或INSERT ... SELECT场景。

很多人的第一反应都是“我这条 SQL 只是查询，凭什么阻塞别人”。但 PostgreSQL 里“查询”和“加锁”并不互斥，真正的问题是：你写的 SQL 在引擎看来到底需要哪种保护级别。以FOR UPDATE为例，它本质上是把读取的行锁定为“我要修改”，防止别的事务在这期间抢先更新或删除。

1.2 表级锁和行级锁不是一回事

要理解联表加锁的问题，得先分清表级锁和行级锁这两个层面。

表级锁控制的是整个表层面的并发操作，比如ALTER TABLE、VACUUM FULL这类 DDL 会拿ACCESS EXCLUSIVE，普通SELECT会拿ACCESS SHARE。表级锁本身通常不产生长时间的阻塞，真正让业务卡死的大多是行级锁。

行级锁有四种模式，按冲突严格程度大致是：

联表查询真正复杂的地方在于：一条语句可能同时对多张表的不同行加锁，加的锁类型还不一样，冲突关系层层叠加，排查难度远超单表操作。

2. 隐藏陷阱一：执行计划重排让锁顺序变得不可控

2.1 现象：两个方向的 JOIN 互等成环

我印象很深的一次事故发生在某个订单统计系统里。两张表，一张是订单表，一张是客户表，业务上有两个不同的后台任务都要锁定同一批关联行做一些处理。

任务A的SQL大致是：

BEGIN; SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'pending' FOR UPDATE; -- 后续对这批行做处理

任务B的SQL是反过来的JOIN顺序：

BEGIN; SELECT * FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.level = 'vip' FOR UPDATE; -- 后续对这批行做处理

两条SQL看起来只是JOIN顺序不同，但在某个时刻同时跑起来，一个先锁了订单表的某些行、再去拿客户表的锁；另一个先锁了客户表的某些行、再去拿订单表的锁。两边都等着对方释放，直接撞成死锁。报警日志里清晰写着deadlock detected，其中一条事务被回滚。

2.2 为什么锁的顺序跟随执行计划

PostgreSQL 的查询优化器会根据统计信息、索引、数据分布，自主决定先访问哪张表、用哪种连接方式。这意味着：你写的 JOIN 顺序不等于执行顺序。同一个查询，今天走嵌套循环先扫小表，明天统计信息变了走了哈希连接，锁的获取顺序就完全变了。

FOR UPDATE的加锁时机和执行节点是绑定的。引擎每扫描到一行符合条件的记录，就会立刻对那一行加锁，而不是等整个查询跑完再统一加。所以执行计划里哪张表先被扫描，哪张表的行就先被锁定。当两个事务持有不同表的锁、又都需要对方的锁时，死锁就无可避免。

这个问题的隐蔽之处在于：平时数据量小、并发低的时候完全看不出来，一旦数据分布变化、执行计划重排，问题才暴露。这也是为什么“昨天还好好的，今天突然死锁”的案例比比皆是。

2.3 如何固定锁顺序

我实践下来比较稳妥的几种做法：

第一种，使用FOR UPDATE OF明确指定要锁的表。比如只关心订单表，就写成：

SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'pending' FOR UPDATE OF o;

这样即使执行计划重排，也只对订单表加锁，不会顺带把客户表锁住，锁的范围小了，冲突概率自然降低。

第二种，把要锁的数据先包进子查询，再在子查询结果上统一加锁：

SELECT * FROM ( SELECT o.id, o.customer_id, o.total FROM orders o WHERE o.status = 'pending' ORDER BY o.id ) sub JOIN customers c ON c.id = sub.customer_id FOR UPDATE OF sub;

这种写法的好处是：锁定操作发生在子查询结果集上，而不是散落在执行计划的各个扫描节点上，锁的顺序变得相对可控。

第三种，如果业务上允许，用ORDER BY在子查询里固定扫描顺序，让所有事务都以同一个顺序获取锁。比如统一按订单ID升序锁定，两个事务就不会交叉等待。

注意：单纯依赖ORDER BY并不保证物理加锁顺序完全一致，但它能显著降低死锁概率，配合FOR UPDATE OF一起用更稳。

3. 隐藏陷阱二：外键约束触发的“连锁拿锁”

3.1 没索引的外键列是隐形杀手

PostgreSQL 的外键约束和 MySQL InnoDB 在锁行为上很不一样。每当更新或删除被引用表（父表）的一行时，数据库需要确认引用表（子表）里没有引用这行的记录，这个确认过程由内建的触发器完成。

问题来了：如果子表的外键列没有索引，引擎只能全表扫描子表来做这个确认。更麻烦的是，确认过程中还要对扫描到的行加锁。这意味着，某业务里父表的一行更新，可能触发对子表上百万元组的扫描和锁检查，整个子表上的并发写入都会被拖慢。

具体锁行为可以这样归纳：

• 往子表插入一行，会在父表对应主键行上加FOR KEY SHARE锁；
• 更新父表的主键列或删除父表行，需要在子表匹配行上加FOR NO KEY UPDATE锁；
• 更新父表其他列但不涉及主键时，一般只需要在子表上做检查，但可能依然持有较弱的锁。

这些锁本身并不一定立刻冲突，但因为全表扫描耗时极长，锁的持有窗口被无限放大，任何需要同一批行锁的操作都会排队。

3.2 用一条 SQL 找出没索引的外键

我排查这类问题时的习惯是直接查pg_constraint，找出所有没有配套索引的外键：

SELECT conname AS constraint_name, conrelid::regclass AS referencing_table, confrelid::regclass AS referenced_table FROM pg_constraint WHERE contype = 'f' AND conindid = 0;

conindid = 0表示这个外键约束没有对应的索引。某次排查一个“删除父表记录越来越慢”的问题，跑这个SQL发现一张业务表的三个外键全都没有索引，每删一条父记录就要对子表做三次全表扫描。补上索引之后，删除时间从分钟级降到毫秒级。

3.3 表结构设计上怎么避免连锁锁

• 建外键约束的列，就算业务上不查，也建议建索引。这不只是为了查询性能，更是为了锁检查不拖垮并发。
• 删除父表数据前评估子表数据量，确认引用检查需要的锁范围。
• 如果父表和子表都是大表，尽量用小事务分批处理，缩短锁持有时间。
• 考虑用逻辑删除代替物理删除，彻底绕开外键检查的锁风暴。

4. 隐藏陷阱三：EvalPlanQual 悄悄重放你的查询

4.1 加锁查询遇到并发修改时的行为演变

EvalPlanQual（EPQ）是 PostgreSQL 里一个非常隐蔽的机制，中文社区习惯叫它“EPQ 重放”。它在READ COMMITTED隔离级别下触发，用于解决：一条加锁语句等到了锁，但它原本读到的那个版本已经变了。

举个常见场景。事务A执行：

BEGIN; SELECT * FROM orders WHERE id = 100 FOR UPDATE;

事务B在这之前已经更新了 order 100 并提交。事务A的SELECT FOR UPDATE会等待事务B结束，然后重新读取 order 100 的最新版本。

到这里还只是行级锁的正常行为，但一旦带上联表查询，事情就变得诡异了。UPDATE ... FROM或SELECT FOR UPDATE带 JOIN 时，如果关联的某个行被并发修改，EPQ 会重新执行整个查询计划的相关部分，用最新的行版本重新计算连接条件。这个重放过程用户根本感觉不到，只能从结果上发现异常。

4.2 联表更新为什么会多出一次“幽灵重试”

某业务系统出现过这样一个现象：一个更新任务明明联表匹配到了目标行，执行后却报告“影响0行”，重跑一次又正常了。查了很久，最后定位到是 EPQ 在作祟。

当时的大致SQL是：

UPDATE orders o SET status = 'processed' FROM customers c WHERE o.customer_id = c.id AND c.level = 'vip' AND o.status = 'pending';

并发场景下，事务A先读取了匹配条件的数据，正准备更新时，事务B抢先修改了某个客户等级或订单状态并提交。事务A拿到锁后，EPQ 用最新数据重新执行连接匹配，发现c.level = 'vip'已经不成立，或者o.status = 'pending'已经不成立，于是放弃更新，影响0行。

这个“重跑一次就好了”的怪象对业务方来说极难理解。因为从快照角度看，第一次查询确实看到了符合条件的行，但真正执行更新时用的却是事务提交后的最新版本，两者存在时间差。

另一个典型表现是：联表更新的结果行数比单表查询出的结果行数少，而且每次少的数据还不一样。如果业务对这个行数敏感，EPQ 就是最可能的元凶。

4.3 如何识别和应对

识别 EPQ 的方法主要靠逻辑推断，因为数据库日志不会直接打“EPQ triggered”之类的话。可以打开log_lock_waits观察等待事件，也可以结合应用日志检查语句执行时间和影响行数的关联。

应对上有几个方向：

• 在REPEATABLE READ或SERIALIZABLE隔离级别下，EPQ 的行为会变成直接报序列化错误，虽然仍然要处理异常，但至少语义更明确；
• 应用层做好重试机制。我通常建议在任务框架里对“影响0行但业务预期要更新”的情况做一次补偿重查，同时打印更新前后的关键字段，方便事后排查；
• 如果业务确实要求“以查询那一刻的条件为准”，可以把条件判断从 SQL 里挪到应用层，先查出主键集合，再按主键精确更新，避免 JOIN 条件在 EPQ 重放时变化。

5. 实操排查：从卡死到定位的一整套 SQL

5.1 第一反应：看 pg_stat_activity 和 pg_locks

遇到锁相关的问题，第一件事不是猜，而是把现场“拍下来”。我现在每次排查都固定跑这套查询：

SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, left(query, 120) AS query, now() - xact_start AS xact_age, now() - query_start AS query_age FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY xact_start;

重点看wait_event_type = Lock的行，wait_event字段会直接告诉你等的是tuple、relation还是transactionid。如果是tuple，说明在等行级锁；如果是relation，说明在等表级锁。

然后通过pg_locks看具体锁对象：

SELECT l.locktype, l.mode, l.granted, a.pid, a.query FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE l.locktype IN ('tuple', 'relation', 'transactionid') ORDER BY a.xact_start;

granted = false的锁就是要不到锁的会话，它是受害方；granted = true且持有冲突锁的会话是嫌疑方。

5.2 用 pg_blocking_pids 追阻塞链

现场只有一个锁等待时很好查，最怕的是多个会话互相阻塞，形成一条链路。这种情况下用pg_blocking_pids最方便：

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, wait_event, left(query, 100) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids会返回阻塞当前pid的所有会话PID数组。一层层往外查，就能把完整阻塞链理出来。我曾经在一次事故里用这个函数从最末端的受害会话一路查到源头ACL限制下的一个开发人员手工执行的SELECT FOR UPDATE，整个过程不到两分钟。

5.3 复现练习：双会话死锁的演示脚本

纸上谈兵不如实际复现一次。我在本地环境经常用下面这套脚本来演示JOIN加锁的交叉等待。

准备两张演示表：

CREATE TABLE customers ( id INT PRIMARY KEY, name TEXT, level TEXT ); CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), status TEXT, total NUMERIC ); INSERT INTO customers VALUES (1, 'A客户', 'vip'), (2, 'B客户', 'normal'); INSERT INTO orders VALUES (10, 1, 'pending', 100), (20, 1, 'pending', 200), (30, 2, 'pending', 300);

会话A先锁定客户1及其订单：

BEGIN; SELECT * FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 1 FOR UPDATE;

然后会话B尝试锁定订单10：

BEGIN; SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.id = 10 FOR UPDATE;

此时B会被A阻塞，因为订单10已被A锁定。这时如果在会话A中再尝试去锁定订单30（属于客户2），恰好又和B的后续需求交叉，就可能触发死锁。实际演练中不一定100%复现同一形态，但锁等待链和死锁报错是能稳定看到的。

5.4 修复落地：改 SQL、加索引、控制事务时长

定位到问题只是第一步，落地修复才是关键。根据不同的根因，我常用的手段有这几个：

• 把FOR UPDATE改成FOR UPDATE OF指定目标表，缩小锁范围；
• 利用子查询包一层，把锁定动作统一到最后一步执行；
• 给外键列和常用的连接列补索引，减少锁持有时间；
• 拆长事务，把批处理切成多个小事务，单事务持锁时间控制在秒级以内；
• 设置idle_in_transaction_session_timeout，强制回收空转的长事务，避免它们占着锁不干活；
• 打开log_lock_waits和合理的deadlock_timeout，让锁等待和死锁事件留下日志，下次不用靠猜。

log_lock_waits的配置很直接：

ALTER SYSTEM SET log_lock_waits = on; ALTER SYSTEM SET deadlock_timeout = '1s';

deadlock_timeout是检测死锁前的等待时长，设得越小，死锁被发现的越快，但太小的值可能会把正常锁等待误报为死锁风险。我一般从1秒起步调。

6. 常见问题速查与习惯养成

6.1 典型症状速查表

6.2 平时怎么防：几条习惯清单

这些经验是我在多次“深夜救火”后沉淀下来的，也成了团队内的强制约定：

第一，禁止裸写FOR UPDATE干拉全部关联表。凡是联表加锁的查询，必须明确FOR UPDATE OF指定目标表，除非你确认所有关联表都需要锁定。

第二，外键列一律建索引。这个约定从建表阶段就执行，比事后补救便宜太多。一张表的所有外键列要是在同一张索引里能覆盖就更好。

第三，任务里的联表查询先用 EXPLAIN 看执行计划。重点看哪张表是驱动表，哪个节点先执行。如果发现两个任务有交叉加锁的可能，调整写法或增加ORDER BY。

第四，长事务零容忍。只要事务持续超过30秒但没有任何 SQL 执行，就要考虑是不是业务代码把事务挂在那边了。配合idle_in_transaction_session_timeout强制兜底。

第五，更新类的联表语句尽量拆成两段。先查出主键集合，再按主键更新。虽然多一次往返，但行为完全可控，不会被执行计划、EPQ 这些机制带偏。

最后分享一个小技巧

我在排查锁问题时，最常用的其实不是复杂的锁视图查询，而是一个很朴素的习惯：先看pg_stat_activity里所有非 idle 会话的xact_start和query_start。xact_start很久但query_start很新的会话，是“开着事务慢慢查”的典型嫌疑；query_start也很久的，则说明SQL本身跑不动了。

另一个小技巧是把常用的锁排查SQL存成视图或函数，需要时直接调用。比如：

CREATE OR REPLACE VIEW v_lock_tree AS SELECT a.pid, a.state, a.wait_event_type, a.wait_event, pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 150) AS query, now() - a.xact_start AS xact_age FROM pg_stat_activity a WHERE a.state <> 'idle' AND a.pid <> pg_backend_pid();

真实环境里时间就是金钱，能在30秒内完成第一轮定位，后面的处置就从“救火”变成了“按流程处理”。

说到底，PostgreSQL 的锁机制并不复杂，复杂的是多条语句、多张表、多个事务叠加之后的组合效应。只要每次写完联表查询都多问一句“这条SQL会锁哪些行、按什么顺序锁”，大半的隐藏陷阱都能提前避开。