上周二凌晨两点某平台的批处理任务又一次卡死在线上了。看日志任务停在那条连了三张表的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 SHAREUPDATE ... 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。表级锁本身通常不产生长时间的阻塞真正让业务卡死的大多是行级锁。行级锁有四种模式按冲突严格程度大致是行级锁模式对应命令与哪些锁冲突FOR KEY SHARE外键检查在父表上加的锁FOR UPDATE 冲突FOR SHARESELECT FOR SHAREFOR UPDATE、FOR NO KEY UPDATE 冲突FOR NO KEY UPDATEUPDATE 不修改主键列时FOR SHARE、FOR UPDATE、FOR NO KEY UPDATE 冲突FOR UPDATEUPDATE 修改主键列、DELETE与所有行级锁冲突联表查询真正复杂的地方在于一条语句可能同时对多张表的不同行加锁加的锁类型还不一样冲突关系层层叠加排查难度远超单表操作。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 加锁查询遇到并发修改时的行为演变EvalPlanQualEPQ是 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. 实操排查从卡死到定位的一整套 SQL5.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尝试锁定订单10BEGIN; 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 典型症状速查表症状最可能原因优先排查点联表查询偶尔卡死重启后恢复某个长事务持有FOR UPDATE锁pg_stat_activity里找xact_age大的会话联表UPDATE影响行数少于预期EvalPlanQual重放并发修改目标行检查隔离级别删除父表一行巨慢外键列无索引pg_constraint里conindid0的记录两个任务同时跑必死锁JOIN执行顺序交叉加锁EXPLAIN看连接顺序FOR UPDATE OF收窄锁范围查询不返回但CPU不高在等锁而非执行等待事件为Lock类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会锁哪些行、按什么顺序锁”大半的隐藏陷阱都能提前避开。