CloudNativePG中单条UPDATE语句会对同表创建多排他锁吗？

场景说明 #

系统使用CloudNativePG管理PostgreSQL实例，包含1个主节点和若干副本节点。执行update t set b = NOT b更新约1亿条数据时，耗时已超19小时。该表关联有物化视图（后续会涉及REFRESH MATERIALIZED VIEW CONCURRENTLY操作）。

锁状态查询语句 #

执行以下SQL查询锁状态：

SELECT 
  l.pid, application_name app, state, query,
  age(clock_timestamp(), state_change) AS change,
  age(clock_timestamp(), query_start) AS age,
  wait_event, mode, granted 
FROM pg_stat_activity p INNER JOIN pg_locks l on p.pid = l.pid 
WHERE query NOT LIKE '% FROM pg_stat_activity %' 
ORDER BY granted, age;

查询结果 #

疑问与解答 #

1. 同一进程（pid相同）的单条语句为何对同表持有多个ExclusiveLock？ #

这是因为你的表t大概率存在TOAST表（PostgreSQL用于存储大字段的附属表），两个ExclusiveLock分别对应主表和其关联的TOAST表。更新操作会同时对主表和TOAST表加锁，因此pg_locks视图会显示多条锁记录。

2. 为何同时持有两个RowExclusiveLock？ #

和上面的原因一致，这两个RowExclusiveLock分别对应主表和TOAST表。UPDATE语句会对目标表及其TOAST表都申请RowExclusiveLock，这是PostgreSQL内部行为，用于保证更新过程中表结构和大字段存储的一致性。

3. 根据PostgreSQL官方文档，ExclusiveLock仅由REFRESH MATERIALIZED VIEW CONCURRENTLY获取，此现象原因是什么？ #

这个理解存在偏差，ExclusiveLock并非仅由REFRESH MATERIALIZED VIEW CONCURRENTLY触发。诸如ALTER TABLE、DROP TABLE这类DDL操作，以及更新操作涉及TOAST表时，都会申请ExclusiveLock。另外，当全表更新涉及关联对象（比如你的物化视图）时，PostgreSQL会通过申请该锁来保证数据一致性。

4. 实例是否在未显式指定的情况下内部执行并发更新操作？ #

不会。单条UPDATE语句在PostgreSQL中由单进程串行执行，不存在内部并发更新的情况。你看到的同一pid多条锁记录，是该进程对不同对象（主表、TOAST表）持有不同锁导致的，并非并发执行多个更新任务。

内容的提问来源于stack exchange，提问作者himanish.k