PostgreSQL中SELECT查询何时获取ExclusiveLock与RowExclusiveLock

基础锁机制认知 #

无特殊语法、无隐式写入逻辑的普通SELECT，在PostgreSQL所有标准隔离级别下，仅会对查询涉及的表加ACCESS SHARE表级共享锁，该锁仅与ACCESS EXCLUSIVE锁互斥，和是否使用LEFT OUTER JOIN等JOIN语法无任何关联，JOIN类型本身不会改变SELECT的默认加锁逻辑，无需在此方向上排查。

SELECT触发RowExclusiveLock/ExclusiveLock的明确场景 #

• 语句本身包含写入或显式加锁逻辑：若SELECT后缀带FOR UPDATE/FOR NO KEY UPDATE/FOR SHARE/FOR KEY SHARE行锁子句，会对命中的行加对应行级锁，表级加ROW SHARE锁；若为INSERT ... SELECT、CREATE TABLE AS SELECT、REFRESH MATERIALIZED VIEW CONCURRENTLY这类将SELECT结果直接用于写入的语法，写入目标表会自动加ROW EXCLUSIVE及以上级别的排他锁。
• 查询触发隐式写入：如果查询调用的自定义函数、计算列、绑定的触发器为VOLATILE类型且内部包含INSERT/UPDATE/DELETE/DDL逻辑，即使外层是纯SELECT语法，执行时也会对被写入的对象加对应级别的排他锁。
• 透明后台逻辑触发写入：若查询触发了同步物化视图刷新、逻辑复制解码写入、审计/安全类扩展插件的日志写入、自动统计信息更新等内置流程，这类操作对用户透明，外层仅感知到执行了SELECT，实际流程中存在写入操作，会持有对应排他锁。
• 锁归属误判：直接查询pg_locks时未过滤会话PID，会把同实例下其他会话的锁、当前事务内之前未提交的写操作持有的锁、autovacuum等后台进程持有的锁，错误归到当前执行的SELECT语句上，这是这类问题最高发的诱因。

对应问题的排查路径 #

你贴出的SQL存在明确别名错误：语句中表别名定义为ga、pg，但JOIN条件和WHERE条件中使用了不存在的gp别名，正常执行会直接抛出语法错误，可先确认实际运行的语句与贴出的内容一致，再按以下顺序排查：

1. 锁归属校验：执行SELECT前先运行select pg_backend_pid();获取当前会话PID，查询pg_locks时增加pid = 刚才获取的PID过滤条件，同时确认当前事务内，在执行该SELECT前有没有未提交的INSERT/UPDATE/DELETE/DDL语句——未提交的写操作持有的排他锁会持续到事务结束，和当前执行的SELECT无关。
2. 隐式逻辑校验：检查group_access_strategy、person_group两张表是否配置了异常触发器、计算列、行级安全策略，这类逻辑中如果包含写入操作，会在SELECT查询时自动触发。
3. 锁类型校验：PostgreSQL表级锁共8个等级，其中SHARE UPDATE EXCLUSIVE是VACUUM、并发建索引操作持有的锁，排查时很容易和EXCLUSIVE锁混淆，需确认锁的具体类型字段值，不要只看锁名称的字面描述。

内容的提问来源于stack exchange，提问作者OldTrace