创建数据库分区时插入触发器偶发死锁问题求助

现有表A，以及按表A的id列进行LIST分区的表B、C、D，表结构定义如下：

CREATE TABLE IF NOT EXISTS TABLE_A
(
    id uuid CONSTRAINT a_guid UNIQUE not null
)

CREATE TABLE IF NOT EXISTS TABLE_B
(
    id uuid CONSTRAINT b_guid UNIQUE not null,
    a_id uuid NOT NULL
CONSTRAINT pk_b PRIMARY KEY (id, a_id)
) PARTITION BY LIST(a_id);

CREATE TABLE IF NOT EXISTS TABLE_C
(
    id uuid CONSTRAINT b_guid UNIQUE not null,
    a_id uuid NOT NULL
CONSTRAINT pk_C PRIMARY KEY (id, a_id)
) PARTITION BY LIST(a_id);

CREATE TABLE IF NOT EXISTS TABLE_D
(
    id uuid CONSTRAINT b_guid UNIQUE not null,
    a_id uuid NOT NULL
CONSTRAINT pk_d PRIMARY KEY (id, a_id)
) PARTITION BY LIST(a_id);

为实现表A插入/删除时自动创建/删除对应分区，编写了以下存储过程及触发器：

CREATE OR REPLACE FUNCTION uuid2TableName(guid uuid) RETURNS text AS $$
BEGIN
  RETURN REPLACE(guid::text, '-', '_');
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION getPartitionedTables() RETURNS text AS $$
BEGIN
  RETURN ARRAY['TABLE_B','TABLE_C','TABLE_D'];
END;
$$ LANGUAGE 'plpgsql';


-- Create new partition tables on triggered

CREATE OR REPLACE FUNCTION createPartitions() RETURNS TRIGGER AS $$
DECLARE
   part_tables text[] = getPartitionedTables();
   part_id text;
   curr_table text;
BEGIN
    part_id = uuid2TableName(NEW.id);

    FOREACH curr_table IN ARRAY part_tables
    LOOP
        execute format('CREATE TABLE %s_%s PARTITION OF %s FOR VALUES IN (''%s'')', curr_table, part_id, curr_table, NEW.id);
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;


CREATE OR REPLACE PROCEDURE deletePartitions(a_id uuid) AS $$
DECLARE
   part_tables text[] = getPartitionedTables();
   part_id text;
   curr_table text;
BEGIN
    part_id = uuid2TableName(a_id::text);

    FOREACH curr_table IN ARRAY part_tables
    LOOP
        execute format('DROP TABLE %s_%s', curr_table, part_id);
    END LOOP;
END;
$$ LANGUAGE 'plpgsql';

触发器定义：

DROP TRIGGER IF EXISTS trigger_new_a_id ON TABLE_A;
CREATE TRIGGER trigger_new_a_id
    AFTER INSERT ON TABLE_A
    FOR EACH ROW
    EXECUTE FUNCTION createPartitions();

DROP TRIGGER IF EXISTS trigger_delete_a_id ON TABLE_A;
CREATE TRIGGER trigger_delete_a_id
    AFTER DELETE ON TABLE_A
    FOR EACH ROW
    EXECUTE PROCEDURE deletePartitions(ROW.id);

异常情况 #

大部分场景运行正常，但集成测试（Java+Spring Boot+Hibernate+PostgreSQL，通过java.sql.Connection插入表A）中偶尔出现死锁：

java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 30220 waits for AccessExclusiveLock on relation 1769359 of database 23886; blocked by process 30175.
Process 30175 waits for AccessShareLock on relation 1769283 of database 23886; blocked by process 30220.
  Hint: See server log for query details.
  Where: SQL statement "CREATE TABLE TABLE_B_1f8e4e73_2f39_455f_9bea_5730553553cd PARTITION OF TABLE_B FOR VALUES IN ('1f8e4e73-2f39-455f-9bea-5730553553cd')"

测试用UUID均为新生成，重复概率极低，无法稳定复现，但流水线测试中多次出现，需排查方向、解决建议及复现方法。

排查方向 #

• 锁顺序不一致：检查并发操作中对B/C/D表的操作顺序是否统一。若两个并发会话分别按B→C、C→B的顺序处理分区，易引发循环等待导致死锁。
• 元数据锁冲突：创建分区时会对主表（如TABLE_B）加AccessExclusiveLock，若同时有其他会话（如查询主表、查看表结构）持有AccessShareLock，会触发锁等待，多会话互相等待则引发死锁。
• 异步测试逻辑冲突：确认集成测试中是否存在并行执行的插入/删除用例，或插入与查询操作并行，导致触发器并发操作分区表。
• 触发器执行时机冲突：AFTER INSERT触发器执行时，若有其他会话正在修改B/C/D的主表或分区，可能引发锁竞争。

解决建议 #

• 固定分区操作顺序：在createPartitions和deletePartitions函数中，统一按TABLE_B→TABLE_C→TABLE_D的顺序处理分区，避免不同会话因操作顺序不一致产生循环等待。
• 提前加锁避免冲突：在创建分区前，先按固定顺序对所有主表加AccessExclusiveLock，确保同一时间只有一个会话处理分区操作，示例：

  CREATE OR REPLACE FUNCTION createPartitions() RETURNS TRIGGER AS $$
  DECLARE
     part_tables text[] = getPartitionedTables();
     part_id text;
     curr_table text;
  BEGIN
      part_id = uuid2TableName(NEW.id);
      -- 先按固定顺序加锁
      FOREACH curr_table IN ARRAY part_tables
      LOOP
          execute format('LOCK TABLE %s IN ACCESS EXCLUSIVE MODE', curr_table);
      END LOOP;
      -- 再创建分区
      FOREACH curr_table IN ARRAY part_tables
      LOOP
          execute format('CREATE TABLE %s_%s PARTITION OF %s FOR VALUES IN (''%s'')', curr_table, part_id, curr_table, NEW.id);
      END LOOP;
      RETURN NEW;
  END;
  $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
  

• 移出触发器中的DDL操作：触发器内执行DDL会持有元数据锁，风险较高。建议将分区创建逻辑改为异步处理（如消息队列触发后台任务），或在应用层插入TABLE_A后同步创建分区，控制并发。
• 优化测试逻辑：集成测试中避免并行执行涉及TABLE_A插入/删除的用例，或在测试前加全局锁，确保分区操作串行执行。

复现方法 #

• 模拟并发插入：用Java多线程同时向TABLE_A插入数据，每个线程生成独立UUID，线程数设为4-8，循环执行插入，重复多次观察死锁。
• 结合查询操作：在并发插入的同时，启动多线程查询TABLE_B/TABLE_C/TABLE_D的主表或分区（如SELECT * FROM TABLE_B LIMIT 1），模拟业务查询场景，提升死锁触发概率。
• 混合插入删除操作：同时运行插入TABLE_A和删除TABLE_A的线程，模拟异步测试中的混合操作场景。
• 开启PostgreSQL详细日志：设置log_statement = 'all'、log_lock_waits = on，死锁发生时可获取完整的语句和锁等待链，定位触发条件。

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