手动合并分区遇死锁，求无需全局排他锁的优化方案

问题背景 #

我有一个维护作业，会在分区达到特定时长时合并它们。但该作业有时会与简单SELECT语句发生死锁，死锁日志如下：

Process 32212 (maintenance) waits for AccessExclusiveLock on relation 22010 (partitioned table) of database 12345; blocked by process 30230.
Process 30230 (select) waits for AccessShareLock on relation 254333841 (partition) of database 12345; blocked by process 32212.

我认为原因在于维护作业初期未显式锁定分区表，仅锁定待合并分区，后续步骤才会短暂锁定分区表，当前执行代码如下：

do $e$ begin   
create table schema_a.new_merged_partition (like schema_a.partitioned_table_p1 including all);
-- locking all the partitions that I will merge:
lock table schema_a.partitioned_table_p7034,schema_a.partitioned_table_p7057,[...]
insert into schema_a.new_merged_partition select * from schema_a.partitioned_table where partition_id in (7034,7057,[...]);
alter table schema_a.new_merged_partition add constraint chk_p check ((partition_id is not null) and (partition_id = any (array[7034,7057,[...]])));
-- not shown: settings grants here
drop table schema_a.partitioned_table_p7034,schema_a.partitioned_table_p7057,[...];
alter table schema_a.new_merged_partition rename to partitioned_table_p7034m;
-- not shown: creating triggers here
alter table schema_a.partitioned_table attach partition schema_a.partitioned_table_p7034m for values in (7034,7057[...]);
alter table schema_a.partitioned_table_p7034m drop constraint chk_p;
end; $e$;

问题： 能否在不一开始就对整个分区表加AccessExclusiveLock的前提下优化该场景，避免死锁？

优化方案 #

可以通过调整锁的获取顺序和操作时序来避免死锁，无需一开始就锁定整个分区表：

• 提前获取分区表的低级别锁
  在作业最开始，先对分区表加ShareLock（而非AccessExclusiveLock）。ShareLock会阻止其他事务修改分区表结构（比如添加/删除分区），但允许SELECT语句获取AccessShareLock，不会阻塞正常查询。这一步能避免后续执行attach partition时才请求AccessExclusiveLock导致的死锁。

• 调整分区删除与挂载的顺序
  先挂载新合并的分区，再删除旧分区。这样SELECT语句在访问旧分区时不会被阻塞到需要等待分区表锁的阶段，减少循环等待的可能性。

• 缩小数据复制的范围
  直接从待合并的分区复制数据，而非查询主表。这样能减少对主表的依赖，降低锁冲突概率。

调整后的示例代码 #

do $e$ begin   
    -- 先对分区表加ShareLock，阻止结构修改但允许查询
    lock table schema_a.partitioned_table in share mode;
    
    create table schema_a.new_merged_partition (like schema_a.partitioned_table_p1 including all);
    
    -- 锁定待合并的分区
    lock table schema_a.partitioned_table_p7034,schema_a.partitioned_table_p7057,[...] in access exclusive mode;
    
    -- 直接从旧分区复制数据，缩小扫描范围
    insert into schema_a.new_merged_partition 
    select * from schema_a.partitioned_table_p7034
    union all select * from schema_a.partitioned_table_p7057
    union all [...] ;
    
    alter table schema_a.new_merged_partition add constraint chk_p check ((partition_id is not null) and (partition_id = any (array[7034,7057,[...]])));
    
    -- 先挂载新分区到主表
    alter table schema_a.new_merged_partition rename to partitioned_table_p7034m;
    -- not shown: creating triggers here
    alter table schema_a.partitioned_table attach partition schema_a.partitioned_table_p7034m for values in (7034,7057[...]);
    
    -- 再删除旧分区
    drop table schema_a.partitioned_table_p7034,schema_a.partitioned_table_p7057,[...];
    
    alter table schema_a.partitioned_table_p7034m drop constraint chk_p;
end; $e$;

关键说明 #

• ShareLock与SELECT需要的AccessShareLock兼容，不会阻塞常规查询，同时能防止其他事务修改分区表结构。
• 先挂载再删除的顺序，让SELECT语句访问目标分区范围时能快速切换到新分区，避免旧分区被锁定后触发的锁等待链。

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